The total no of blocks in lis table is 1408000
regards
anandkl
Printable View
The total no of blocks in lis table is 1408000
regards
anandkl
tables Partitioned?
I got this site abt optimizer...
http://download-west.oracle.com/docs...3/optimops.htm
HTH
Ohh com on dave, tell me how will oracle estimate (Estimator phase) the number of blocks be fetched ( before execution phase )?Quote:
Originally posted by slimdave
It's not the % of rows that matters, it's the % of blocks that they represent.
Will it directly estimate no of blocks that needs to be fetched?
Ofcourse its the blocks that matters, but to calculate/estimate number of blocks oracle will take the help of Stats (column/table) correlates/applies with QUERY in question and estimate, right?.
So, dont you think oracle will first evaluate/estimate %of recs that it needs to fetch. if otherwise why do you think Histograms are necessary?
Anand :
You still didnt answer wass the Card of both cols...
BTW, are histograms taken since you used Bit Map Type I wud guess the card of the cols is too low & taking histograms will help you.
Abhay.
Estimating the number of rows is just a step towards estimating the number of blocks, based on the index clustering factor (I suggest a search ofdocumentation for the phrase "clustering factor"). Once Oracle has estimated how many blocks it will require from the table, the number of rows becomes an irrelevance in deciding whether to use FTS or index, except as far as it affects later stages of the execution plan.Quote:
Originally posted by abhaysk
Ohh com on dave, tell me how will oracle estimate (Estimator phase) the number of blocks be fetched ( before execution phase )?
Will it directly estimate no of blocks that needs to be fetched?
Ofcourse its the blocks that matters, but to calculate/estimate number of blocks oracle will take the help of Stats (column/table) correlates/applies with QUERY in question and estimate, right?.
So, dont you think oracle will first evaluate/estimate %of recs that it needs to fetch. if otherwise why do you think Histograms are necessary?
Sure, it estimates the number of rows required from the table, but that could mean returning 1% of the blocks or all of the blocks. That is why it is the % of blocks that matters, and why the idea that Oracle will use an index for "% of rows less than n" is yet another myth.
It might sound just a step to you, but to me its significant & this is what i was trying to point/emphaisze in my previous post.Quote:
Originally posted by slimdave
Estimating the number of rows is just a step towards estimating the number of blocks, based on the index clustering factor (I suggest a search ofdocumentation for the phrase "clustering factor").
Ok, in my earlier post I shud have been presize of using % of Blocks rather than % of rows...Quote:
Originally posted by slimdave
Once Oracle has estimated how many blocks it will require from the table, the number of rows becomes an irrelevance in deciding whether to use FTS or index, except as far as it affects later stages of the execution plan.
yes, but i think you didnt read the post completelyQuote:
Originally posted by slimdave
That is why it is the % of blocks that matters,
Please note am not telling "% rows" is all that matters, but i meant its significant, as more often than not you get a range of values in a block/blocks over spreading into all/most of the blocks in the table.Quote:
Ofcourse its the blocks that matters, but to calculate/estimate number of blocks oracle will take the help of Stats (column/table) correlates/applies with QUERY in question and estimate, right?.
I aggree, but i think my post ofQuote:
Originally posted by slimdave
and why the idea that Oracle will use an index for "% of rows less
than n" is yet another myth.
is driving you crazy ;)Quote:
What is the % of recs being selected?.
Well, all i can say its a common practice to use recs over blocks as one would assume it(recs) to be presnt in small no(%) of blocks & cant imagine for n values(recs) to spread accross n blocks ( Ofcourse there is a chance of being so, but rare ).
Abhay.
This is important, I think.Quote:
Originally posted by abhaysk
Anand :
You still didnt answer wass the Card of both cols...
If the cardinality is high, bitmap indexes aren't useful.
There's no reason to assume the l.date_add column has low cardinality, as well as the l.ad_id column.
Try to create b-tree indexes instead of the bitmap ones.
Blocks is what matters, it is the smallest logical unit Oracle manages NOT rows. It´s very common people think Oracle manages rows
It depends on how you configure your storage parameters, you can alter your cluster factor with those parameters. Having small amount of rows in tons of blocks is not rare, just how you configure PCTUSED, PCTFREE, INITRANS, MAXTRANS
So I do agree with Slimdave
Same number of rows different number of blocks, different excution planCode:
create table a_low_storage pctfree 90 pctused 5 nologging as
select
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN
from dba_tab_columns;
create table a_high_storage pctfree 1 pctused 90 nologging as
select
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN
from dba_tab_columns;
create unique index a_low_storage_idx1 on a_low_storage(owner, table_name, column_name);
create unique index a_high_storage_idx1 on a_high_storage(owner, table_name, column_name);
analyze table a_low_storage compute statistics;
analyze table a_high_storage compute statistics;
set autotrace traceonly exp stat
select * from a_high_storage where owner = 'SYS' and (table_name like 'A%' or
table_name like 'B%' or
table_name like 'C%' or
table_name like 'D%');
3278 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=437 Bytes=30153)
1 0 TABLE ACCESS (FULL) OF 'A_HIGH_STORAGE' (Cost=20 Card=437 Bytes=30153)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
350 consistent gets
0 physical reads
0 redo size
261332 bytes sent via SQL*Net to client
24623 bytes received via SQL*Net from client
220 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3278 rows processed
select * from a_low_storage where owner = 'SYS' and (table_name like 'A%' or
table_name like 'B%' or
table_name like 'C%' or
table_name like 'D%');
3278 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=132 Bytes=9108)
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=3 Card=1 Bytes=69)
3 2 INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=2 Card=1)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=3 Card=1 Bytes=69)
5 4 INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=2 Card=1)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=3 Card=1 Bytes=69)
7 6 INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=2 Card=1)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=3 Card=1 Bytes=69)
9 8 INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1911 consistent gets
0 physical reads
0 redo size
274348 bytes sent via SQL*Net to client
24623 bytes received via SQL*Net from client
220 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
3278 rows processed
Number of rows would be used in JOINs needed to reduce the result set as small as possible
Quote:
Originally posted by guru_heaven
Blocks is what matters, it is the smallest logical unit Oracle manages NOT rows. It´s very common people think Oracle manages rows
It depends on how you configure your storage parameters, you can alter your cluster factor with those parameters. Having small amount of rows in tons of blocks is not rare, just how you configure PCTUSED, PCTFREE, INITRANS, MAXTRANS
So I do agree with Slimdave
Same number of rows different number of blocks, different excution plan
Number of rows would be used in JOINs needed to reduce the result set as small as possible
Ok, I would say having PCTUSED 5 is a dumb idea, but any how letme try to show you how Number Of Rows play
a significant role. I dont analyze index, but i just take table/column stats, now see the behaviour.
Well From that time i am telling you guys '% ROWS' is not all it matters, but play significant role in calculating numberCode:TEST:SYS> analyze table A_high_STORAGE compute statistics for table for all columns size 254 /* note no index analyzed */;
Table analyzed.
TEST:SYS> analyze table A_low_STORAGE compute statistics for table for all columns size 254 /* note no index analyzed */;
Table analyzed.
TEST:SYS> alter session set optimizer_mode=all_rows;
Session altered.
TEST:SYS> set autotrace traceonly;
TEST:SYS> select * from A_high_STORAGE where owner='ABHAY' and table_name like 'T%';
1049 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=61 Bytes=43
31)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'A_HIGH_STORAGE' (Cost=4
Card=61 Bytes=4331)
2 1 INDEX (RANGE SCAN) OF 'A_HIGH_STORAGE_IDX1' (UNIQUE) (Co
st=2 Card=61)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
344 consistent gets
0 physical reads
0 redo size
28619 bytes sent via SQL*Net to client
1258 bytes received via SQL*Net from client
71 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1049 rows processed
TEST:SYS>
TEST:SYS> select * from A_low_STORAGE where owner='ABHAY' and table_name like 'T%';
1049 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=59 Bytes=41
89)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=4 C
ard=59 Bytes=4189)
2 1 INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cos
t=2 Card=59)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
423 consistent gets
5 physical reads
0 redo size
28619 bytes sent via SQL*Net to client
1258 bytes received via SQL*Net from client
71 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1049 rows processed
of blocks based on Cluster Factor as Slimdave said. Now i have not taken index stats and oracle thinks index scan is
better over FTS because it simply unable to estimate the number of blocks.
Guru i dont think in any type if environment PCTUSED will be less than 40....
Abhay.
In the test case PCTFREE is what matters not PCTUSED, what are you talking about PCTUSED? I made PCTUSED low because PCTUSED + PCTFREE cannot be > 100
And who on earth would analyze the table and not the index if you are gonna use CBO?
just had a closer look at your test, try the one below, at least make the test same not with your *own* tables
Code:
analyze table a_low_storage delete statistics;
Table analyzed.
analyze table a_high_storage delete statistics
Table analyzed.
analyze table A_low_STORAGE compute statistics for table for all columns size 254;
Table analyzed.
analyze table A_high_STORAGE compute statistics for table for all columns size 254;
Table analyzed.
select * from a_low_storage where owner = 'SYS' and (table_name like 'A%');
1379 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=83 Card=1421 Bytes=98049)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=83 Card=1421 Bytes=98049)
2 1 INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=4 Card=1421)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
818 consistent gets
0 physical reads
0 redo size
116616 bytes sent via SQL*Net to client
10526 bytes received via SQL*Net from client
93 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1379 rows processed
select * from a_high_storage where owner = 'SYS' and (table_name like 'A%');
1379 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1443 Bytes=99567)
1 0 TABLE ACCESS (FULL) OF 'A_HIGH_STORAGE' (Cost=20 Card=1443 Bytes=99567)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
224 consistent gets
0 physical reads
0 redo size
110505 bytes sent via SQL*Net to client
10526 bytes received via SQL*Net from client
93 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1379 rows processed