Re: 100.000 $ Reward (Oracle 8 versus SQL 7)

David,

1. That's true SQL knows only how to create indexes based simple columns (not expressions). If you say so that Oracle 8 can , I believe you... BUT SQL is smart enough and permits computed columns and if you used the computed column (instead of the function) SQL take automatically advantage of the indexes of the columns that compose the computed column(if available). BTW Oracle can create indexes based on multiple columns (and take advantage on this)?.

The point is that in the article from http://www.dbpd.com/vault/9801xtra.htm at paragraph 4 it was mentioned that (I quote): "Do not make indexes on those columns that are used only with functions and operators in the WHERE clause of queries." and that's not true for SQL this is what I sustained !.

2) I don't know if SQL 7 uses bitmap indexes (probably not) but uses (internally) the index intersection and unions for sure (read the documentation and checked with the execution plans). BUT you've missed the point again.

3) All my statements referred to the mentioned article. It's no reference on the bitmap indexes in that one ;-0

4) That's not entirely true as well (did you really check your assertions ?!):
I am quoting from documentation :
"To use partitioned views, you create several identical tables, specifying a constraint to determine the range of data that can be added to each table. The view is then created using these base tables. When the view is queried, SQL Server automatically determines which tables are affected by the query and references only those tables. ".
Obviously the above refers to the query horizontally partitions. (I haven't mentioned the creation of the partitions and vertically partitions ...)

5) here is another example that you didn't read carefully may statements. It is no reference in that article to fact tables and warehouse related issues. So your example is out of scope.

6) definitely you have to re-check your assertions (anyway the 6) point is again out of scope !!!):
FYI, I am quoting again from technical doc's: "The query optimizer recognizes certain commonly occurring database and query structures, such as star or snowflake schemas, and chooses an execution plan based on a careful cost analysis particularly well-adapted to queries against these structures. For example, the SQL Server 7.0 query optimizer uses the techniques of Cartesian product index manipulation and semi-joins to process queries against star schemas. "
about the reward ... I am still waiting for min 5 assertions about what I've mentioned in my first article (read carefully ;-)).

Dan.

David Sisk wrote in message ...
>Okey dokey:
>
>Using Oracle8.1 as a reference:
>
>1) SELECT statements with a function applied to the WHERE clause will use
>an index (with no modification of syntax) if (all other parameters held
>constant and suitable) the index is created on the function of the column
>rather than the column itself. SQLServer7 can't do that.
>
>2) The cost-based optimizer will determine which index(es) to use or
>whether to do full table scans. With bitmap indexes, the CBO can do unions
>and intersections of the indexes to get the necessary row ID's to retrieve.
>SQLServer7 can't do that.
>
>3) Speaking of bitmap indexes, SQLServer7 doesn't have them. Only B-tree
>indexes are available.
>
>4) Oracle can automatically perform partition-elimination on partitioned
>tables and partitioned indexes. SQLServer7 can't do that. In fact,
>SQLServer7 doesn't support any form of partitioning.
>
>5) Oracle provides summary management, which allows a query directed at a
>large fact table to be transparently redirected to a much smaller summary
>table, completely behind the scenes with no modification in SQL syntax.
>SQLServer7 can't do that.
>
>6) (Just for good measure) Oracle recognizes dimensional models (ie.
"star
>schemas") if they are designed correctly, and will automatically use "star
>join" processing algorithms rather than more traditional relational
>algorithms. SQLServer7 can't do that.
>
>If you don't buy any of this, check the benchmarks on www.tpc.org and/or
the
>documentation on technet.oracle.com.
>
>Email me and I'll reply with the mailing address you can send the $100,000
>check to. :=)
>
>Best regards,
>
>--
>David C. Sisk
>Need tech info on Oracle? Visit The Unofficial ORACLE on NT site at
>http://www.ipass.net/~davesisk/oont.htm
>Like original modern rock? Listen to song samples and buy a CD at
>http://www.mp3.com/disparityofcult
>
>
>Dan G wrote in message <7qoogi$h44$1_at_pollux.ip-plus.net>...
>>Check this out first !:
>>http://www.dbpd.com/vault/9801xtra.htm
>>
>>Second: I am not an Microsoft fan (even if I am using MS SQL).
>>
>>Allmost all Oracle commandments (applicable to SQL 7) doesn't need to be
>>applied with SQL 7 because the query optimizer is smart enough.
>>Allmost all Oracle enhancemets (written in the article) apllied as well
>with
>>SQL 7.
>>The one who can give more than 5 examples against the above statements
>worth
>>the prize ;-).
>>
>>All the best.
>>
>>
>>
>>
>>
>
>
Received on Wed Sep 08 1999 - 08:03:56 CDT