On Sun, Feb 24, 2008 at 11:57:46PM +0300, Ruslan Zakirov wrote: > Hello, guys. >
Privet Ruslan ;) (and thanks for commiting my patches in svn :)) > I'm reviewing this again after receiving new info from various sources. > 1) People say that our function based indexes are incorrect, instead > of LOWER('XXX') we must use LOWER(XXX), where XXX is name of a column. > 2) This particular query we build by hand without using our abstract > interface, so it even don't have any calls to LOWER() function. > > So you should try: > 1) replace all indexes that has LOWER('XXX'), list of indexes we > create by default is in etc/schema.Oracle Done, no change, see my comment on #8970. > 2) create index on Groups(Type, Domain, Instance) instead of one I > suggested before and explain query without any LOWER calls. > I tried, but it isn't needed, actually, with the following custom indexes (made as I said, for other speed improvements), _and_ after running dbms_utility.analyze_schema('RT', 'compute'), every indexes are used: My current custom indexes: CREATE INDEX FSHACL1 ON ACL (OBJECTID); CREATE INDEX FSHCGM1 ON CACHEDGROUPMEMBERS (DISABLED, MEMBERID); CREATE INDEX FSHGROUPMEMBERS1 ON GROUPMEMBERS (MEMBERID); CREATE INDEX FSHGROUPS1 ON GROUPS (INSTANCE); CREATE INDEX FSHPRINCIPALS1 ON PRINCIPALS (DISABLED); CREATE INDEX FSHTICKETS1 ON TICKETS (STATUS); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1489 Card=1 Bytes=256) 1 0 SORT (ORDER BY) (Cost=1489 Card=1 Bytes=256) 2 1 NESTED LOOPS (Cost=1488 Card=1 Bytes=256) 3 2 VIEW (Cost=1487 Card=1 Bytes=10) 4 3 SORT (UNIQUE) (Cost=1487 Card=1 Bytes=77) 5 4 HASH JOIN (Cost=1087 Card=45693 Bytes=3518361) 6 5 INLIST ITERATOR 7 6 INDEX (RANGE SCAN) OF 'ACL1' (NON-UNIQUE) (Cost=1 Card=24 Bytes=696) 8 5 NESTED LOOPS (Cost=1085 Card=15527 Bytes=745296) 9 8 HASH JOIN (Cost=542 Card=54344 Bytes=1249912) 10 9 NESTED LOOPS (Cost=15 Card=19856 Bytes=297840) 11 10 TABLE ACCESS (BY INDEX ROWID) OF 'PRINCIPALS' (Cost=14 Card=89822 Bytes=988042) 12 11 INDEX (RANGE SCAN) OF 'FSHPRINCIPALS1' (NON-UNIQUE) (Cost=335 Card=179644) 13 10 INDEX (UNIQUE SCAN) OF 'USERS_KEY' (UNIQUE) 14 9 INDEX (FULL SCAN) OF 'GROUMEM' (NON-UNIQUE) (Cost=3050 Card=983335 Bytes=7866680) 15 8 TABLE ACCESS (BY INDEX ROWID) OF 'GROUPS' (Cost=1 Card=1 Bytes=25) 16 15 INDEX (UNIQUE SCAN) OF 'GROUPS_KEY' (UNIQUE) 17 2 TABLE ACCESS (BY INDEX ROWID) OF 'USERS' (Cost=1 Card=1 Bytes=246) 18 17 INDEX (UNIQUE SCAN) OF 'USERS_KEY' (UNIQUE) Statistics ---------------------------------------------------------- 223 recursive calls 0 db block gets 924231 consistent gets 35 physical reads 0 redo size 1249 bytes sent via SQL*Net to client 275 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 8 sorts (memory) 0 sorts (disk) 0 rows processed I can enhance a little bit with the following index: CREATE INDEX TEST1 ON CACHEDGROUPMEMBERS(MEMBERID); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1161 Card=1 Bytes=256) 1 0 SORT (ORDER BY) (Cost=1161 Card=1 Bytes=256) 2 1 NESTED LOOPS (Cost=1160 Card=1 Bytes=256) 3 2 VIEW (Cost=1159 Card=1 Bytes=10) 4 3 SORT (UNIQUE) (Cost=1159 Card=1 Bytes=77) 5 4 HASH JOIN (Cost=759 Card=45693 Bytes=3518361) 6 5 INLIST ITERATOR 7 6 INDEX (RANGE SCAN) OF 'ACL1' (NON-UNIQUE) (Cost=1 Card=24 Bytes=696) 8 5 NESTED LOOPS (Cost=757 Card=15527 Bytes=745296) 9 8 NESTED LOOPS (Cost=214 Card=54344 Bytes=1249912) 10 9 NESTED LOOPS (Cost=15 Card=19856 Bytes=297840) 11 10 TABLE ACCESS (BY INDEX ROWID) OF 'PRINCIPALS' (Cost=14 Card=89822 Bytes=988042) 12 11 INDEX (RANGE SCAN) OF 'FSHPRINCIPALS1' (NON-UNIQUE) (Cost=335 Card=179644) 13 10 INDEX (UNIQUE SCAN) OF 'USERS_KEY' (UNIQUE) 14 9 TABLE ACCESS (BY INDEX ROWID) OF 'CACHEDGROUPMEMBERS' (Cost=1 Card=3 Bytes=24) 15 14 INDEX (RANGE SCAN) OF 'TEST1' (NON-UNIQUE) 16 8 TABLE ACCESS (BY INDEX ROWID) OF 'GROUPS' (Cost=1 Card=1 Bytes=25) 17 16 INDEX (UNIQUE SCAN) OF 'GROUPS_KEY' (UNIQUE) 18 2 TABLE ACCESS (BY INDEX ROWID) OF 'USERS' (Cost=1 Card=1 Bytes=246) 19 18 INDEX (UNIQUE SCAN) OF 'USERS_KEY' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1091728 consistent gets 1580 physical reads 0 redo size 1235 bytes sent via SQL*Net to client 275 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed Maybe we should try to review the SQL to minimize NON-UNIQUE indexes uses, but that's outside my current knowledge :( _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com