Follow up to Sql question
April 29, 2008 - 12pm Central time zone
Reviewer: ripudaman nanda from Colchester UK
Sir,
I was refering to my sql programmer writing
select Max(rate) from rate_table
where a=x
In this case will it be helpful to have columns having computed values and or using reverse key
index or materialised views
many thanks for your previous answers
regards
ripu
Followup April 29, 2008 - 6pm Central time zone:
how about just an index on (a,rate)
nothing fancy.
where a = x - can be used to find the last entry for a=x (or the first, in this case we want the last)
and that last entry of course has the max rate.
ops$tkyte%ORA9IR2> create index t_idx on t(a,rate);
Index created.
ops$tkyte%ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows=>1000000, numblks=> 10000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select max(rate) from t where a = 5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 FIRST ROW (Cost=2 Card=10000 Bytes=260000)
3 2 INDEX (RANGE SCAN (MIN/MAX)) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=100)
see the index (range scan (min/max)) there? that did the minimal work to find that rate.
and hopefully the programmer doesn't have to write a query like that, for the programmer knows that procedural slow by slow code is bad and they should do most of their work in a single SQL statement :)
binds
April 30, 2008 - 12am Central time zone
Reviewer: Martyn from Australia
I realise that bind variables are the right thing in the vast majority of cases but "a thumb rule
that says in all cases bind varibles queries will give faster results than using strings" might be
overstating it.
We have a query with massively skewed data (an index on postcode with more than half the records in
one postcode and the remainder scattered) and found bind variable peeking killing some of our
queries. So I think that would be an exception to the rule unless there is a way around the peeking?
Followup April 30, 2008 - 10am Central time zone:
i'll err on the side of do it with binds, and when we hit a problem, I'll explain then when to use literals.
cause, you know, if you see me write "use literals", you know what will happen?
I'll never see another bind ever again.
This is what I learned from you
April 30, 2008 - 6am Central time zone
Reviewer: Karthick from India
This is what I learned from you. Below is an extract from your presentation on the topic do we
always need bind vairable.
-- Always say 'Never say Never'
-- Never say 'Always'
-- You do not want to
- Over Bind
- Always Bind
Over Binding
Compulsive disorder to eradicate all literals in SQL
Brought on by taking good advice to an illogical extreme
Begin
for x in ( select object_name
from user_objects
where object_type in ( `TABLE', `INDEX' ))
loop
....
-- Do we need to bind those?
-- Might it be a bad thing to bind those?
-- Literals in Static SQL are perfectly OK
-- Problem is neither VB nor Java do Static SQL
- JSQL is considered ¿dead¿
- So, it becomes very hard to tell what is truly variable
-- Bind only that which varies
-- Literals for everything else
Always Binding
-- Data warehouse ¿ no way.
-- When you run queries per second, yes.
-- When you run queries that take seconds, maybe, maybe no.
-- Consider the frequency of the query
- 5,000 users running reports. Bind
- 50 users data mining. No Bind
- OLTP. Bind
- End of month report. Maybe No Bind.
- Common Sense, it is all about math
-- But remember SQL Injection!
-- That password screen, binds
-- Typical queries, binds
-- Only the queries that need the advantage of literals during optimization!
- And those have to be looked at over and over
- 'user dump dest', it seemed so simple
Followup April 30, 2008 - 10am Central time zone:
I know, I've written that before. Absolutely.
But in answer to:
"should I bind"
I will say
"yes"
see above.
|