База данных только для отчетов, импортируется еженочно. После этого происходит сбор статистики.
Запрос выполняется от 3х минут без нагрузки и более
(ожидания sequental read)
Код:
select
sum(decode( d.doctype, 'CS', 1,-1)*s.totalprice), count( distinct d.rowid), count(s.rowid)
from
supermag.smdocuments d, supermag.smspec s
where d.doctype=s.doctype
and d.id=s.docid
and d.doctype in ('CS' ,'CR')
and d.createdat between '01.12.2007' and '30.12.2007'
Код:
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1486 Card=1 Bytes=66)
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'SMSPEC' (TABLE) (Cost=2 Card=15 Bytes=480)
3 2 NESTED LOOPS (Cost=1486 Card=14208 Bytes=937728)
4 3 PARTITION LIST (INLIST) (Cost=28 Card=971 Bytes=33014)
5 4 TABLE ACCESS (FULL) OF 'SMDOCUMENTS' (TABLE) (Cost=28 Card=971 Bytes=33014)
6 3 INDEX (RANGE SCAN) OF 'SMCSPEC_DISPLAYPOS' (INDEX) (Cost=1 Card=35)
Почему TABLE ACCESS (FULL) OF 'SMDOCUMENTS ?
Железо: 1 проц Core 2, 2 гига оперативки, 3 SATA винчестера, по ним все раскидано.
Параметры:
Код:
*.audit_file_dest='d:\oracle\product\10.2.0\admin\\adump'
*.background_dump_dest='d:\oracle\product\10.2.0\admin\\bdump'
*.compatible='10.2.0.4.0'
*.control_files='d:\oracle\oradata\\control01.ctl','d:\oracle\oradata\\control02.ctl','d:\oracle\oradata\\control03.ctl'
*.core_dump_dest='d:\oracle\product\10.2.0\admin\\cdump'
*.db_block_size=8192
*.db_cache_size=37748736
*.db_file_multiblock_read_count=32
*.db_keep_cache_size=104857600
*.db_recovery_file_dest='d:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XDB)'
*.job_queue_processes=10
*.open_cursors=300
*.optimizer_index_caching=50
*.optimizer_index_cost_adj=50
*.pga_aggregate_target=549453824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=786432000
*.sga_target=734003200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:\oracle\product\10.2.0\admin\\udump'
Код:
SMDOCUMENTS
PARTITION BY LIST(DOCTYPE)
CREATE INDEX SUPERMAG.SMDOCUMENTS_CLIENT
ON SUPERMAG.SMDOCUMENTS(CLIENTINDEX)
CREATE INDEX SUPERMAG.SMDOCUMENTS_CREATEDAT
ON SUPERMAG.SMDOCUMENTS(CREATEDAT)
CREATE INDEX SUPERMAG.SMDOCUMENTS_LOC
ON SUPERMAG.SMDOCUMENTS(LOCATION)
CREATE INDEX SUPERMAG.SMDOCUMENTS_LOCFROM
ON SUPERMAG.SMDOCUMENTS(LOCATIONFROM)
CREATE INDEX SUPERMAG.SMDOCUMENTS_LOCTO
ON SUPERMAG.SMDOCUMENTS(LOCATIONTO)
SMCDOCUMENTS_PK Primary (DOCTYPE, ID)
SMSPEC
PARTITION BY LIST(DOCTYPE)
CREATE Unique INDEX SUPERMAG.SMCSPEC_DISPLAYPOS
ON SUPERMAG.SMSPEC(DOCTYPE,DOCID,DISPLAYITEM)
CREATE INDEX SUPERMAG.SMSPEC_ART
ON SUPERMAG.SMSPEC(ARTICLE,DOCTYPE,DOCID)
CREATE INDEX SUPERMAG.SMSPEC_CAUSEIDX
ON SUPERMAG.SMSPEC(CAUSETYPE,CAUSEID,CAUSESPECITEM)
SMCSPEC_PK Primary (DOCTYPE, DOCID, SPECITEM)