Post by originalni posterTreba nam verzija baze, veličina tablica,tip
tablica(partitioning),explain plan i podaci
selektivnosti indeksa koji se koriste-ako se koriste.
Aj super. Evo ti:
10.2.0.5
Znači ovo je neoptimalni plan. (Nemam pojma zašto radi SS na indeksu,
nisam prije primjetio.)
bi.kalendar je tablica s jednim retkom za svaki dan u godini.
select *
from bi.intervali u,
bi.kalendar k
where k.day between u.datum_od and u.datum_do
and k.day between '01052010' and '31102010';
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1301214371
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25508 | 9M| 4126 (43)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | MERGE JOIN | | 25508 | 9M| 4126 (43)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | INTERVALI | 55140 | 8400K| 3085 (24)| 00:00:01 |
|* 4 | INDEX SKIP SCAN | I_INTERVALI_DATUM_OD_DO | 55140 | | 220 (52)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | SORT JOIN | | 185 | 46065 | 10 (40)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| KALENDAR | 185 | 46065 | 8 (25)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_KALENDAR_DAY | 185 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE('01052010')<=TO_DATE('31102010'))
4 - access("U"."DATUM_DO">='01052010' AND "U"."DATUM_OD"<='31102010')
filter("U"."DATUM_DO">='01052010')
5 - filter("K"."DAY"<="U"."DATUM_DO")
6 - access("K"."DAY">="U"."DATUM_OD")
filter("K"."DAY">="U"."DATUM_OD")
8 - access("K"."DAY">='01052010' AND "K"."DAY"<='31102010')
I neki statistički podaci:
SQL> select column_name, num_distinct
2 from sys.user_tab_col_statistics
3 where table_name = 'INTERVALI'
4 and column_name in ('DATUM_OD', 'DATUM_DO');
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
DATUM_OD 140
DATUM_DO 150
SQL> select avg(datum_do - datum_od)
2 from intervali;
AVG(DATUM_DO-DATUM_OD)
----------------------
33,278067
SQL> select index_type, table_name, blevel, leaf_blocks,
2 distinct_keys, avg_leaf_blocks_per_key,
3 avg_data_blocks_per_key, clustering_factor
4 from sys.user_indexes
5 where index_name = 'I_INTERVALI_DATUM_OD_DO';
INDEX_TYPE TABLE_NAME BLEVEL LEAF_BLOCKS
---------------------- -------------------- --------- -----------
NORMAL INTERVALI 2 750
...
DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
------------- ----------------------- ----------------------- -----------------
646 1 42 27385
Bolju sliku o podacima dobiješ kada napraviš materijalizirani view
od prvog querija i indeksiraš datum iz kalendara.
CREATE MATERIALIZED VIEW BI_MV_INTERVALI
...
AS
select *
from bi.intervali u,
bi.kalendar k
where k.day between u.datum_od and u.datum_do
and k.day between '01012008' and '31122011';
SQL> select num_rows, blocks
2 from sys.user_tab_statistics
3 where table_name = 'BI_MV_INTERVALI';
NUM_ROWS BLOCKS
--------- ---------
2960284 67665
SQL> select index_type, table_name, blevel, leaf_blocks,
2 distinct_keys, avg_leaf_blocks_per_key,
3 avg_data_blocks_per_key, clustering_factor
4 from sys.user_indexes
5 where index_name = 'I_BI_MV_INTERVALI_DATUM';
INDEX_TYPE TABLE_NAME BLEVEL LEAF_BLOCKS
--------------------------- ------------------- --------- -----------
NORMAL BI_MV_INTERVALI 2 7909
DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
------------- ----------------------- ----------------------- -----------------
1095 7 90 99488
E sad. AVG_LEAF_BLOCKS_PER_KEY se lako smanji kompresijom indeksa. Ovaj
AVG_DATA_BLOCKS_PER_KEY je problematičan.
--
Milan Mimica
http://sparklet.sf.net