I can suggest a few reasons why Oracle's count(*) might go faster using index scans.
In Oracle, table data blocks are stored separate from index data (unless you create an index clustered table). Contrast that with SAP-DB where all tables are stored as B-tree indexes, so that the leaf nodes of the index structure contain the table data. When you get to the index leaf node in Oracle, you have an entry that does not contain table data. It points to the data by a rowid that consists of the database file, block within the file, and row within the block where the table data is located. To do a count of the rows of the table, you only have to scan the leaf nodes of the index, which can be quite small relative to scanning the whole table. The small size increases the likelihood of it being cached. Furthermore (maybe SAPDB is like this too?) the leaf nodes have forward and backward pointers allowing the index scan to occur without having to traverse the index for each row. I have to point out that there are advantages to storing the tables as SAP-DB does when it comes to update, since accessing the table data for a given row requires one less I/O. If Oracle is faster scanning the table without an index (as in the case "select count (SomeNullableColumnWithNoIndex) from sometable "), it may be because Oracle can scan the table without touching the index at all. Also, in the Oracle world, you define a tablespace (their rough equivalent of a collection of devspaces) and control what tables (or indexes) go in what tablespace. Therefore you can locate the table data for a given table in its own tablespace separate from its indexes and other tables, which forces the table's data blocks very close together. This increases the likelihood that table scans will be sequential reads. They also recognize scan situations and read multiple blocks to reduce the number of physical reads (does the SAP-DB parameter _MULT_IO_BLOCK_CNT do this for SAP-DB?). Regards, Mary Edie Meredith [EMAIL PROTECTED] On Tue, 2002-11-26 at 03:47, Becker, Holger wrote: > ajit_cus wrote: > > > This is my way of optimizing my count(*) queries under SAPDB: > > 1>Select Count(*) from testtable > > Takes 1:14 secs returns 240830 > > This is the output of explain: > > TABLE SCAN > > RESULT IS COPIED > > > > Instead I use this: > > 2>Select Count(aNotNullColumnWithanIndex) from testtable > > Takes 12 seconds returns 240830 > > This is the output of explain: > > SINGLE INDEX COLUMN USED (INDEX SCAN) > > RESULT IS COPIED > > As you can see 2 is lot faster than 1 and should always give > > the same result. I only wish that SAPDB was smart enough to > > do that internally. > > > > With version 7.4 your wish became true and if I find some time > I'll integrate this feature into version 7.3. > > > However this can't be the way other DB's function as they are > > still a lot faster than the index scan method. Under oracle > > any count query (irrespective of index's on columns) seems to > > always run in under 1 sec (trying this on an ORACLE table > > with 150000 records-->note that it's identical in schema to > > my SAPDB test table.) > > I suppose ORACLE updates a row counter for every insert and delete. > But I'm not sure because this could let into concurrency problems. > I'm appreciate for every hint on this topic. > > > I wonder if there are any SAPDB parameters that affect > > performance of count queries? > > no > > Kind regards, > Holger > SAP Labs Berlin > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
