I have got a table table with 21 millions records of which 20 millions meet the criterion col1= ‘text’. I then started iteratively to set the value of col2 to a value unequal to NULL. After I have mutated 10 million records, the following query got slow, that was fast in the beginning: I noticed that as soon as I
Tag: oracle11g
SQL CASE Statement for no data
I have a table that has process engines 1,2,3,4,5,6 with a running status. When one of the engines is down the record gets deleted from the table. Using a case statement I can display the first engine that is down but how do I go about displaying the engines if 2 or more engines are down. For e.g. how do
SQL query to find greatest in columns and rows
How does one in oracle find the greatest of three columns in say 10 rows? The requirement is I have three dates column and I have need to find greatest of three columns in 10 rows. I know greatest will find in one row. How? Answer How about
How to mask a mobile phone number portion with ‘X’
Using Oracle SQL, how can I replace certain numbers with an ‘X’. For example, if I have a random mobile number: 0400 100 200 or 0400100200 I would like to mask out the number to: 0400 XXX XXX …
How to find schema name in Oracle ? when you are connected in sql session using read only user
I am connected to a oracle database with a read only user and i used service name while Setting up connection in sql developer hence i dont know SID ( schema ). How can i find out schema name which i am connected to ? I am looking for this because i want to generate ER diagram and in that
Nth max salary in Oracle
To find out the Nth max sal in oracle i’m using below query But According to me by using the above query it will take more time to execute if table size is big. i’m trying to use the below query but not getting output.. any suggetions please .. Please share any link on how to optimise queries and decrease
Oracle: Single multicolumn index or two single column indexes
I have table create table1( column1 number(10, column2 number(10), column3 number(10) ); column1 is Primary Key column2 and column3 is Foreign key I have created unique constraint on 2 …
Query Oracle for 12 month moving total (SUM) of data
I need to run a query on some data that brings back the moving 12 month total. I’ve already performed the average function on the data… so now I just need to loop through and get a moving 12 month period of data and SUM it up so I can graph it. Below is the current query i’m issuing but
Find out the nth-highest salary from table
name salary —– —– mohan 500 ram 1000 dinesh 5000 hareesh 6000 mallu 7500 manju 7500 praveen 10000 hari 10000 How would I find the nth-highest salary from the …
Weekday number regardless of the NLS settings
I’m looking for the simplest way to determine the weekday number for the DATE value in oracle independent of the NLS settings. Any ideas? Answer ISO weeks start on Monday; they don’t use NLS settings. I think this expression is reliable. To show how the arithmetic works . . . current_date is a Wednesday. In general, if you can’t find