95 reputation
7
bio website
location
age
visits member for 1 year, 8 months
seen Jul 12 at 0:05

Jul
8
comment How to update ID=null values in table to incremental counter values?
This looks like the cursor solution I previously used. That one simply updated with an incrementing number, and it would have taken about 3 days in my case for about 20 million new IDs.
Jul
8
answered How to update ID=null values in table to incremental counter values?
Jul
2
awarded  Curious
Jun
28
asked How to update ID=null values in table to incremental counter values?
Jun
13
comment Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?
I finally managed to implement the above Union-Subselect syntax, since it was a very specific part of the code. Oracle now uses all my indices and has decent performance. Even SQL Server performs notably better (25%:75% showing exe plans for both queries), although it never fell back to the full-table-scan disaster seen in Oracle. I wonder if the databases can't do any better, or if it still was a statistics problem.
Jun
5
awarded  Commentator
Jun
5
comment Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?
Currently, the Tasks table has more than 300000 rows, ObjectAffectingTasks only 20 (to become more), where tasks were started on parent/container objects. Finally, the direct/indirect task ratio (obejctID in Tasks vs AffectingTasks table) may be 3 to 1. The number of tasks per object ID is usually 5 to 20. However, the number of rows in Tasks and also the left joined tables is quite huge.
Jun
5
revised Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?
edited body
Jun
5
asked Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?
Mar
3
revised oracle crash with ORA-00942: table or view does not exist
short headline with description of cause
Mar
3
answered oracle crash with ORA-00942: table or view does not exist
Jan
28
revised Does Oracle ADO.NET varchar parameter size influence query plan reuse?
added 17 characters in body
Jan
28
revised Does Oracle ADO.NET varchar parameter size influence query plan reuse?
cursor sharing setting
Jan
28
asked Does Oracle ADO.NET varchar parameter size influence query plan reuse?
Jan
1
comment Is using multiple foreign keys separated by commas wrong, and if so, why?
Do you really think a string with comma separated child IDs is helpful? I mean, the application had to read first, then parse the IDs and query all the children, like select * from DealCategories where DealId in (1,2,3,4,...). You have more experience, regarding database design, than me, so maybe you have good reason in some cases for such "extreme tuning" in very specific cases. My only idea to justify this is a very high select load on Deal/DealCategory. This looks to me much like some outsource team without any DB design knowledge, beyond creating tables, created it.
Dec
31
comment Get all items from list table which don't have a successful log entry for 'sent' in log table
yes, list_id first, then action and failure_reason.
Dec
31
comment Should I add multiple secondary indexes to search
sorry, MichaelD, wrong advice. Different parameterization requires different query plans, a stored procedure will create only one for all (or be treated like a bloated Ad-Hoc query). If no parameter is submitted, it should not be in the query plan! These optional parameters with null checks must go away!
Dec
31
answered Is using multiple foreign keys separated by commas wrong, and if so, why?
Dec
28
comment Should I add multiple secondary indexes to search
@Kenneth Fisher: True if the main table is very broad, the Data columns and others are big, like nvarchar(4000/max) columns. Or if the index can restrict the search, such as an index on country and the names, or country, zip, names, with a restriction on the first.
Dec
28
comment Should I add multiple secondary indexes to search
@user636525 If you're coding in .NET, using ADO.NET, you will likely build them like this: var sql=new StringBuilder("SELECT * FROM Customer"); then, for the parameters to be filtered by, such as sql.Append("WHERE Zip=@paramzip"); (NOT: "WHERE Zip=" + myZip.ToString()!!!). Predefining all possible SQL is possible (if Country is mandatory, only 8), but maybe too much effort for little effect. You will need at least 4 indices (3 if country mandatory, each with Country), one for each searched column. I would at least add one more column to each: Country,Zip ; Country,FirstName etc