Skip to content

Instantly share code, notes, and snippets.

@gregrahn

gregrahn/indexes.sql

Created June 19, 2014 19:05
Show Gist options
  • Save gregrahn/2be9144d0a047c98a561 to your computer and use it in GitHub Desktop.
Save gregrahn/2be9144d0a047c98a561 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
indexes.sql
create unique index person_pk on person(person_id);
create index person_n1 on person(date_id);
create unique index calendar_pk on calendar(date_id) ;
Raw
populate.sql
declare
v_start_date date := to_date ('20060101', 'yyyymmdd');
v_days number := 364;
v_persons_per_day number := 32768;
v_birth_date date;
v_date_id number (8);
begin
for i in 0 .. v_days
loop
v_birth_date := v_start_date + i;
v_date_id := to_number (to_char (v_birth_date, 'YYYYMMDD'));
insert into calendar
values (v_date_id,
upper(trim(to_char (v_birth_date, 'month'))),
case
when v_birth_date between to_date ('2005-dec-23', 'yyyy-mon-dd') and to_date ('2006-jan-20', 'yyyy-mon-dd')
then 'CAPRICORN'
when v_birth_date between to_date ('2006-jan-21', 'yyyy-mon-dd') and to_date ('2006-feb-19', 'yyyy-mon-dd')
then 'AQUARIUS'
when v_birth_date between to_date ('2006-feb-20', 'yyyy-mon-dd') and to_date ('2006-mar-20', 'yyyy-mon-dd')
then 'PISCES'
when v_birth_date between to_date ('2006-mar-21', 'yyyy-mon-dd') and to_date ('2006-apr-20', 'yyyy-mon-dd')
then 'ARIES'
when v_birth_date between to_date ('2006-apr-21', 'yyyy-mon-dd') and to_date ('2006-may-21', 'yyyy-mon-dd')
then 'TAURUS'
when v_birth_date between to_date ('2006-may-22', 'yyyy-mon-dd') and to_date ('2006-jun-21', 'yyyy-mon-dd')
then 'GEMINI'
when v_birth_date between to_date ('2006-jun-22', 'yyyy-mon-dd') and to_date ('2006-jul-22', 'yyyy-mon-dd')
then 'CANCER'
when v_birth_date between to_date ('2006-jul-23', 'yyyy-mon-dd') and to_date ('2006-aug-21', 'yyyy-mon-dd')
then 'LEO'
when v_birth_date between to_date ('2006-aug-22', 'yyyy-mon-dd') and to_date ('2006-sep-23', 'yyyy-mon-dd')
then 'VIRGO'
when v_birth_date between to_date ('2006-sep-24', 'yyyy-mon-dd') and to_date ('2006-oct-23', 'yyyy-mon-dd')
then 'LIBRA'
when v_birth_date between to_date ('2006-oct-24', 'yyyy-mon-dd') and to_date ('2006-nov-22', 'yyyy-mon-dd')
then 'SCORPIO'
when v_birth_date between to_date ('2006-nov-23', 'yyyy-mon-dd') and to_date ('2006-dec-22', 'yyyy-mon-dd')
then 'SAGITTARIUS'
when v_birth_date between to_date ('2006-dec-23', 'yyyy-mon-dd') and to_date ('2007-jan-20', 'yyyy-mon-dd')
then 'CAPRICORN'
end
);
for j in 1 .. v_persons_per_day
loop
insert into person
values (i * v_persons_per_day + j,
v_date_id
);
end loop;
commit;
end loop;
end;
/
Raw
queries.lst
SQL>
SQL> select /*+ gather_plan_statistics */ count(*)
2 from person p ,calendar c
3 where p.date_id = c.date_id and month = 'MAY'
4 /
COUNT(*)
----------
1015808
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0zhhaw8ywyt85, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from person p ,calendar
c where p.date_id = c.date_id and month = 'MAY'
Plan hash value: 1463406140
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.46 | 2479 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.46 | 2479 |
| 2 | NESTED LOOPS | | 1 | 1015K| 1015K|00:00:00.01 | 2479 |
|* 3 | TABLE ACCESS FULL| CALENDAR | 1 | 31 | 31 |00:00:00.01 | 7 |
|* 4 | INDEX RANGE SCAN | PERSON_N1 | 31 | 32768 | 1015K|00:00:00.01 | 2472 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("MONTH"='MAY')
4 - access("P"."DATE_ID"="C"."DATE_ID")
23 rows selected.
SQL>
SQL> select /*+ gather_plan_statistics */ count(*)
2 from person p ,calendar c
3 where p.date_id = c.date_id and zodiac = 'TAURUS'
4 /
COUNT(*)
----------
1015808
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cfhqyz5d6kap0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from person p ,calendar
c where p.date_id = c.date_id and zodiac = 'TAURUS'
Plan hash value: 1463406140
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.40 | 2478 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.40 | 2478 |
| 2 | NESTED LOOPS | | 1 | 1015K| 1015K|00:00:00.01 | 2478 |
|* 3 | TABLE ACCESS FULL| CALENDAR | 1 | 31 | 31 |00:00:00.01 | 7 |
|* 4 | INDEX RANGE SCAN | PERSON_N1 | 31 | 32768 | 1015K|00:00:00.01 | 2471 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ZODIAC"='TAURUS')
4 - access("P"."DATE_ID"="C"."DATE_ID")
23 rows selected.
SQL>
SQL> select /*+ gather_plan_statistics */ count(*)
2 from person p ,calendar c
3 where p.date_id = c.date_id and zodiac = 'TAURUS' and month = 'MAY'
4 /
COUNT(*)
----------
688128
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8cgu0gassdjg2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from person p ,calendar
c where p.date_id = c.date_id and zodiac = 'TAURUS' and month = 'MAY'
Plan hash value: 1463406140
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.29 | 1682 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.29 | 1682 |
| 2 | NESTED LOOPS | | 1 | 688K| 688K|00:00:00.01 | 1682 |
|* 3 | TABLE ACCESS FULL| CALENDAR | 1 | 21 | 21 |00:00:00.01 | 7 |
|* 4 | INDEX RANGE SCAN | PERSON_N1 | 21 | 32768 | 688K|00:00:00.01 | 1675 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("ZODIAC"='TAURUS' AND "MONTH"='MAY'))
4 - access("P"."DATE_ID"="C"."DATE_ID")
23 rows selected.
SQL>
SQL> spool off
Raw
queries.sql
set lines 132
spool queries
select /*+ gather_plan_statistics */ count(*)
from person p ,calendar c
where p.date_id = c.date_id and month = 'MAY'
/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
select /*+ gather_plan_statistics */ count(*)
from person p ,calendar c
where p.date_id = c.date_id and zodiac = 'TAURUS'
/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
select /*+ gather_plan_statistics */ count(*)
from person p ,calendar c
where p.date_id = c.date_id and zodiac = 'TAURUS' and month = 'MAY'
/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
spool off
Raw
run.sql
set echo on
@tables.sql
@populate.sql
@indexes.sql
@stats.sql
Raw
stats.lst
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'CALENDAR', '(MONTH, ZODIAC)') FROM DUAL;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'CALENDAR','(MONTH,ZODIAC)')
------------------------------------------------------------------------------------------------------------------------------------
SYS_STUWHPY_ZSVI_W3#C$I3EUUYB4
SQL>
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS
3 (
4 OWNNAME => USER
5 ,TABNAME => 'CALENDAR'
6 ,METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY'
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS
3 (
4 OWNNAME => USER
5 ,TABNAME => 'PERSON'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT
2 TABLE_NAME,
3 COLUMN_NAME,
4 NUM_DISTINCT as NDV,
5 NUM_BUCKETS,
6 SAMPLE_SIZE,
7 HISTOGRAM
8 FROM
9 USER_TAB_COL_STATISTICS
10 WHERE TABLE_NAME IN('CALENDAR','PERSON')
11 ORDER BY 1,2;
TABLE_NAME COLUMN_NAME NDV NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
------------------------------ ------------------------------ ---------- ----------- ----------- ---------------
CALENDAR DATE_ID 365 254 365 HEIGHT BALANCED
CALENDAR MONTH 12 12 365 FREQUENCY
CALENDAR SYS_STUWHPY_ZSVI_W3#C$I3EUUYB4 24 24 365 FREQUENCY
CALENDAR ZODIAC 12 12 365 FREQUENCY
PERSON DATE_ID 365 1 11960320 NONE
PERSON PERSON_ID 11960320 1 11960320 NONE
6 rows selected.
SQL>
SQL> spool off
Raw
stats.sql
set lines 132
spool stats
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'CALENDAR', '(MONTH, ZODIAC)') FROM DUAL;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(
OWNNAME => USER
,TABNAME => 'CALENDAR'
,METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY'
);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(
OWNNAME => USER
,TABNAME => 'PERSON'
);
END;
/
SELECT
TABLE_NAME,
COLUMN_NAME,
NUM_DISTINCT as NDV,
NUM_BUCKETS,
SAMPLE_SIZE,
HISTOGRAM
FROM
USER_TAB_COL_STATISTICS
WHERE TABLE_NAME IN('CALENDAR','PERSON')
ORDER BY 1,2;
spool off
Raw
tables.sql
drop table person purge;
create table person
(
person_id number(10) not null
,date_id number(8) not null
)
;
drop table calendar purge;
create table calendar
(
date_id number(8) not null
,month varchar2(16) not null
,zodiac varchar2(16) not null
)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment