I have a query with a few tables in join, and I filter data with something like
where mytable.initial_date > sysdate-30
If I use any date, it takes about 5 seconds to run, but if I use
to_date('01010001','ddmmrrrr')
instead of any other dates, it takes just a few milliseconds. Now, it's just a curiosity, but why that date makes the query VERY fast? Does Oracle treat it in some special way? Maybe it knows every date is greatest than that date and doesn't consider the filter?
I have a query optimized as to it indexes and others runs immediately when the answer is few records in SQL Server such as Oracle, however when the result is large eg 20,000 records all data access times are very diferent. The query returns many fields (about 20) and some of them are of type Varchar 250 and some of 2000 I understand here may be the problem, but not is because for similar results (20,000 records) sql run in 2 seconds and Oracle but it responds little to have full data takes around 30 seconds. The problem is really in bringing information to all these fields since if the inquiry it also but only returning a numeric field is done in 2 seconds. Tests I've done them both through ODBC, in the Toad as in the own Oracle console on the server, so it is not problem Driver or flow of data through the network, I would like to think that this is some of the settings I think there is as much difference between Oracle and Sql. The databases are ORACLE 10 and SQL Server 2008.
I am using 10g and want to let an user to delete a single record from a multirow form via delete button. This button is supposed to do the same, what the "delete record" button on the default menu does. (I want a custom menu, so I can't use that button)
My delete button ist assigned to a control-block and the when-button-pressed-Trigger of that button looks like this: ------------- DECLARE TEMP VARCHAR(30); BEGIN -- Record chosen? IF :<BLOCK>.ID IS NOT NULL THEN
[Code]...
The problem is, that the alert asks the user about the previously chosen record correctly, but if the user clicks BUTTON1 the Form says: No changes to save (FRM-40401).
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
mucking on an Oracle 11.2 database, simple range partitioning issue. Seems using a "complex" formula inside the AT clause annoys it? Or am I doing something wrong?
I create the table with RANGE partition just fine:
CREATE TABLE my_part_tab ( id number, sdate date ) PARTITION BY RANGE ( sdate ) ( PARTITION P2013Q1 VALUES LESS THAN ( TO_DATE('01-jan-2013','dd-mon-yyyy') ), PARTITION P2013Q2 VALUES LESS THAN ( TO_DATE('01-jul-2013','dd-mon-yyyy') ), PARTITION P2013Q3 VALUES LESS THAN ( TO_DATE('01-oct-2013','dd-mon-yyyy') ),
[code]...
Table created.(and yes, I'm aware of INTERVAL option that'll do this next part "automagically", however, INTERVAL and REFERENCE partitioning are incompatible, and the child table is using REFERENCE partition). to make things easier on DBA for future, I'm trying to create a script that makes added a year's worth of partitions less manual.So far, I have the following working:
ACCEPT lYear PROMPT "Add Paritions for which calendar year?" ALTER TABLE my_part_tab SPLIT PARTITION PMAX AT ( TO_DATE('01-apr-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q1,PARTITION PMAX ); ALTER TABLE my_part_tab SPLIT PARTITION PMAX AT ( TO_DATE('01-jul-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q2,PARTITION PMAX );
[code]...
But no luck ...Same issue with other variations:
ALTER TABLE my_part_tab SPLIT PARTITION PMAX AT ( (ADD_MONTHS(TO_DATE('01-jan-&lYear','dd-mon-yyyy'),12)) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX ); ALTER TABLE my_part_tab SPLIT PARTITION PMAX AT ( (TO_DATE('01-jan-'||TO_CHAR(TO_NUMBER('&lYear')+1,'fm9999'),'dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
I want the query to resolve overlapping dates as well as merge contiguous segment and leave non-contiguous segments as is. The final result of the query should be like this.
All our production DBs are backed up by some Hitachi tool (apparently it is called ShadowImage) . Apparently they put all the tablespaces in Backup mode and 'take a snap' . Our BAU team says this is faster.
On an event of DB Crash Is it easier to restore, recover from these 'snapshots' ? Are these snapshots much faster than RMAN ?
I have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.
Here is the query using WITH:
with prev as ( select person_uid, id, name, academic_period, case when enrolled_ind = 'Y' and registered_ind = 'N' and student_status = 'AS' then 0 else 1 end as enreg_stat,
We need to setup a test system using data from one of the offsite customer location.The option we would like to use is impdp with network link. Below given step will make import faster if we exclude indexes and constraints
Steps Import schema excluding constraints,rf_constraints,indexes with metadata only Import schema with data only spool ddl using dbms_metadata.get_ddl from Source for constraints,rf_constraints and indexes execute on Destination the Index creation ddl execute on Destination constraints and rf_constraints ddl
i have a view in a schema of my database 'vw_get_noti_calendar_data' and i want to select some field but it shows an error 'invalid number' and i have observed it but no effect,so where is the error.
my code is---------
SELECT * FROM aepuser.vw_get_noti_calendar_data WHERE TO_DATE (TO_CHAR (event_start_date, 'mm/dd/yyyy'), 'dd/mm/yyyy') BETWEEN TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy') AND TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy') AND srnum BETWEEN 5 * 1 + 1 AND 5 * 1 + 5;
building SQL query to get the result as shown below.
Create Table Temp
CREATE TABLE TEMP ( CASEID NUMBER, SATUS VARCHAR2(1 BYTE), TRANS_DATE DATE ) Insert data
[Code]...
I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.
Restarting oracle services through services.msc is faster then manual (In command prompt).
Even If we have heavy transaction on the oracle database But we can restart the database within minutes by using services.msc But It will take more time in command prompt(sql>shu immediate). So shall we assume here that services.msc is using shut abort command while restarting the database?
I want a monthly report where the month wise sum of qty should be displayed in a row for last 12 months. I need to specify the month start date and end date in the query to pick the sum for the particular month. How can i do it in a SQL query?
SUM(decode(to_char(t1.trx_date,'mm/rrrr'), to_char(add_months(SYSDATE, -1),'mm/rrrr'), nvl(t2.quantity_invoiced, 0))) AS qty_01 from t1,t2 where t1.col1=t2.col1
instead of sysdate i have to use start and end of month. Also i am using group by clause on some columns.
I want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time FROM LOG_TIMESTAMPS where ProdID = 1680988889 group by ProdRequestID ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
DIDDOB_INFO(VARCHAR2) 34425 Mar 1967 123 12 MAR 1963;25 FEB 1974;25 AUG 1978 46515 FEB 1980
I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.
My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.
In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.
Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.
Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.
1) CNT_VAL is the pseudo column: ----------------------------- CK_IDPI_IDEFF_DT TERM_DT CNT_VAL Mem1ABC1-Jan-1331-Mar-131 Mem1ABC1-Apr-1331-May-131 Mem1ABC1-Jun-1330-Sep-131
[code].....
My Query : ----------
I not getting the desired output here as the value in pseudo column is 3.
select CK_ID, PI_ID,EFF_DT,TERM_DT, (case when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT) when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1) else to_char(nvl(case_CONT,0))
I am trying to replace a date range in a query. Don't understand why it's not working, below is the dummy query for the same:
/* v_Employee:= 'select emp_name from employee_table where Join_Year=~Replace1~ and Release_Month <=~Replace2~'; v_Replace1:=2013; v_Replace2:=7; pos := 1; WHILE (INSTR(v_Employee, '~', 1, pos) <> 0) LOOP IF (pos = 1) THEN
[code]....
Since actual code is like CLOB type, so I have not provided the same. Here after executing this query i found that v_Employee query is getting replaced by v_Replace1 at both of his position i.e "select emp_name from employee_table where Join_Year=2013 and Release_Month <=2013"
But my actual result should: "select emp_name from employee_table where Join_Year=2013 and Release_Month <=7"
i have project for hotel , i can't solve query to search empty room between date . i have table for room , when room record under reservation between date . then i want to found empty room between date, which is query to make do.
Table Room Room_No Type_Room From_Date To_Date Status
Now i need to found empty room between date search room empty between
11/03/2009 - 13/03/2009
select Room_No from Room where From_Date Not between '11/03/2009' and '13/03/2009' i found hard to query.
I am trying to get the last 7 days of record from today date, this query runs every night and I always want the last 7 days. Example - today is 3/13/2013 so I want record from 3/7/2013 to 3/13/2013 and tomorrow it would be 3/8/2013 to 3/14/2013
