- about the wheel, yes that was the point I was trying to make with "don't try to optimize by hypothesize." The most common reason I hear for not using a builtin feature "we heard it was slow"

both

meaning, it is somewhat circular.

you start by coding what you commented, then you maintain them both as you fix bugs.

the old code would have been commented way before it became old code!

Nice one - definitely.

Ahh, got it - yes, you would not comment OUT old code, you would fix old code and fix old comments to reflect new code :)

The are useful to

a) give to support to show "see, with these stats, we get the wrong plan" without giving support all 10 billion rows of data to reproduce the issue

b) after you restore production to QA (to prove you can restore it), you might gather statistics there (on QA) and test your application with the new statistics - to verify that the changed query plans (that is mostly why we gather statistics after all - to cause plans to change!) are for the better - not for the worse. And after you verify that they are for the good - you export from QA and import into production (after saving productions values, just in case)

for example.

</code> http://asktom.oracle.com/Misc/instrumentation.html <code>

dual is read alot :)

every page on htmldb applications - read your session state from .... a table.

I use select to read from tables...

You can set the flag in a table and read it..
You can pass it in a URL...


Use of an appropriate data structure can be very useful here. Say you wanted a table - a simple:

select parameter_value from my_parameter_table where parameter_name = :bind;

would likely work BEST as a single table hash cluster - we'll hash the bind variable, get the database block address (DBA) and get that block - no index range scan, one LIO.

Hashing hopefully is something known to us - this is just like a "hash table" in a data structures class - it is a typical "hashing function". We take the "key" for a row (you designate a hash key, a column/columns from the table). We HASH that key (typically hash function - just like dbms_utility might do with the hash function in that package). That HASHed value is really the address of the row.

When you insert - we hash the key to figure out where to PUT the row.

When you query, we hash the key you provide again to find out where we PUT the row and go there to GET the row.

<quote src = Expert Oracle: Database Architecture>

Hash Clustered Tables

Hash clustered tables are very similar in concept to the index clustered tables just described with one main exception: the cluster key index is replaced with a hash function. The data in the table is the index; there is no physical index. Oracle will take the key value for a row, hash it using either an internal function or one you supply, and use that to figure out where the data should be on disk. One side effect of using a hashing algorithm to locate data, however, is that you cannot range scan a table in a hash cluster without adding a conventional index to the table. In an index cluster, the query

select * from emp where deptno between 10 and 20

would be able to make use of the cluster key index to find these rows. In a hash cluster, this query would result in a full table scan unless you had an index on the DEPTNO column. Only exact equality searches (including IN lists and subqueries) may be made on the hash key without using an index that supports range scans.

In a perfect world, with nicely distributed hash key values and a hash function that distributes them evenly over all of the blocks allocated to the hash cluster, we can go straight from a query to the data with one I/O. In the real world, we will end up with more hash key values hashing to the same database block address than fit on that block. This will result in Oracle having to chain blocks together in a linked list to hold all of the rows that hash to this block. Now, when we need to retrieve the rows that match our hash key, we might have to visit more than one block.

Like a hash table in a programming language, hash tables in the database have a fixed “size.” When you create the table, you must determine the number of hash keys your table will have, forever. That does not limit the amount of rows you can put in there.
</quote>

full scan: we know all of the extents - which have a file# and start block/end block. We know ALL of the blocks we need to read (a data block address is a file.block)

index access: well, we sort of get the rowid from the index, the rowid contains the file.block.


I don't see how this is chicken and egg at all???


We always have the data block address before going after the block - else we'd never know what to go after!

but, and this is important, was it good for you?

;)

you need representative data, yes.