Search results for query: *

When I implemented this, I did actually use rownum, and TOP x on SQLServer (probably shouldnt mention that name in this forum :) ), partly because they are what I am more familiar with. I have dabbled with the analytical stuff in the past, and it was a lot slower then - haven't revisited it to...

That will just restict it to the first (arbitrary if unsorted) rows though, so assuming the data was names in alphabetical order Zachary Zimminy is going to feel awfully ignored :) That said tho, if number of rows inserted equalled the total allowed, then that could be assumed a fail.... Seems...

Im over simplifying the setup, but it works a little like this. A query gets run and the id's of the records it matches are stored in a table as INSERT INTO RESULTS (SID, ID) SELECT 123,ID from (...user configured query) This becomes the list of things the user will work on, and workflow is...

Im wondering if there is a way of forcing a rollback and error if an SQL statement attempts to insert more than X rows into a table (its not an empty table, but a table used as storage for the results of another query). The value X is a varying value (on a per user basis), so probably cant use a...

I see what you're saying about the skip scan, and that does kinda match my mumblings from above, but none of the traces mention a SKIP SCAN (would you expect to see that in the plan, or is it hidden?). I will look into the stats stuff you mention in a bit as it made little sense to me so I will...

Thanks for the replies. All the indexes and primary keys mentioned in the example exist, and the explain plan says they are being used. In the plan, it shows its using the compound indexes directly without a table scan - only the reviews table part uses a table scan. Im assured the statistics...

I have a query that in certain conditions exhibits unusual behaviour when performing an index range scan. For example, I have 3 tables (simplified below, but note that its just an example to illustrate the problem and not my actual tables). Table RECORDS has 2 columns, ID number(20), STATUS...

Just an FYI This is a bug, has been reported to Oracle and they have confirmed and assigned it someone for fixing.

I am seeing a strange problem whilst using sqldeveloper that defies explanation :) I have a *really* simple 3 table query that works when written one way and doesnt when written slightly differently e.g. :- select * from table1 T1 inner join table2 T2 on T1.id1 = T2.id1 inner join...

Thanks taupirho, that is exactly what I needed.... I had come across it while searching, but in all the examples I saw or it it was like "bitand(5,3) would return 1" etc, or it involved RAW stuff, so I (wrongly) assumed it was a PL/SQL thing and kinda discounted it without trying it. I feel...

I am looking for a way to "mask" bits using an AND in a select statement, or equivalent. I need to replicate a select statement we have on SQLServer, that strips of the top bits of a number and returns the max value without them e.g. :- select max(cast(entity_id as bigint) & 0x0ffff) from...

ERROR - $deity not found or out of range :D (sorry couldnt resist) Appologies for the messy sql, its pretty much a dump of the code output with a bit of indenting - I should have cleaned it up a bit more. I did try both ways in Oracle and hit other problems (not being able to outer join to a...

ooohhhhhhh theres PLENTY wrong with that picture. Most our dev is outsourced - I just have to try and make it work well when we get it back from them. Right from the off I have been begging for a DBA/db developer - but hey, that would reduce the all important budget for shiny new BMW's for the...

At the moment, we have a system for displaying formatted results. As this is just a "picker" list, which displays a set of "user defined" details about a record, and on clicking that row - the full record details are displayed. The list is meant to be *indicative* of the data contained, not...

You have to refresh the list before they will show. Right click on "tables" in the tree on the right, and choose refresh

The hints didnt seem to make any noticable difference to the overally time of the query, and in a way im glad, as none of these queries will be "hard coded" and are all completely dynamic, so a "pure" query relying on the optimizer would be prefered to a hand tuned one. The one area I hadnt...

Im kinda curious myself why such a difference, im not a database guy, just a programmer with enough SQL knowlege to get me into trouble (and a company too tight to hire a DBA, but doesnt worry about getting a fleet of BMW's for the sales team....grrrr) :) Today, the other queries do seem to...

Just as an FYI - The performance of the suggested solutions was unfortunately unusable in our environment, so I had to give up on using the condition in the from clause, and had to put it in the where - select people.name, phone.number from people PE left outer join phone PH on...

Sorry for the missunderstanding .... I am working on Oracle 10g at the moment (but in a multi database environment - originally sqlserver2000 tho). Its just that earlier today I was working on SQLServer2005 looking at speeding up our paged results code, and came across the "new" Microsoft...

Thanks for the reply - your first example works for me (I think your second is for sqlserver2005 tho? ). It does seem slower than the sqlserver equivalent, but allows me to carry on working for now and benchmark it properly later. I do feel pretty stupid now as its a fairly obvious answer, I...