SQL & PL/SQL :: Date Search - No Index Created On Table
Feb 25, 2010
DT1 is a column of date datatype and there is no index created on the table
I want to add the below lines in the sql
TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
Which one should I use in where condition to query and why?
1. TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
2. DT1 BETWEEN '01/01/2005' AND '31/12/2009' (as NLS date format will not change)
3. DT1 BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND ('31/12/2009' ,'dd/mm/yyyy')
View 6 Replies
ADVERTISEMENT
Jun 30, 2011
how to know weather procedure is working or not in a database ..
how to know index is created on table or not.
View 2 Replies
View Related
Feb 22, 2011
Me working on Oracle application Express,there are lots of tables created in it, how will i get to know who created which table and on which date.
View 7 Replies
View Related
Mar 28, 2012
I want to find the history of manipulation the database by filtering the user who created the last tables, what tables are, when he created it etc ..
I'm using oracle XE and the client is toad.
View 3 Replies
View Related
Aug 16, 2012
I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?
STARTDT_ ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012
View 6 Replies
View Related
Dec 27, 2011
I have created queue table using below script
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE (
QUEUE_TABLE => 'scott.NG_MSG_QUEUE_TBL',
QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE',
COMPATIBLE => '10.0.0',
[code].......
Indexes
--------
INDEX_NAME TABLESPACE_NAME
------------------------------ --------------------------
AQ$_NG_MSG_QUEUE_TBL_T MEDIUM_DATA
AQ$_NG_MSG_QUEUE_TBL_I MEDIUM_DATA
SYS_IL0001100359C00037$$ MEDIUM_DATA
SYS_IL0001100359C00040$$ MEDIUM_DATA
SYS_IL0001100359C00041$$ MEDIUM_DATA
SYS_C0073180 MEDIUM_DATA
I want all the index to be created in different tablespace like 'medium_index. can we specify tablespace for index when we are creating queue table.
View 1 Replies
View Related
May 17, 2011
what my issue is with this virtual column
CREATE TABLE C0HARPA.VCOL_TAB
(
col1 VARCHAR2(30 BYTE),
v_col1 VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(col1,1,6)) VIRTUAL
)
TABLESPACE TOOLS
[code]...
Lastly, can an index be created on a virtual column? I am thinking no since it calculated?
View 3 Replies
View Related
Jul 30, 2010
SELECT department_id
FROM (SELECT department_id
FROM employees
UNION
SELECT department_id
FROM employees_old )
WHERE department_id=100;
[code]....
The index has been created on both depart_id for the two tables. The only difference between the two I observed was the 1 recursive call for the 1st sql.and also, one additional view in the plan.There is a little difference in bytes sent over the network.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
[code]....
Is there any performance impact you find in those above two sqls if you compare?
View 14 Replies
View Related
Nov 2, 2011
I have a create a report for our dept with certain criteria. So far what I need is a report that shows last weeks numbers or sign ups. I can't get the date search or between to work.
What I need is one for month end and one for last 7 days.
here's what I have so far.
select ap.name
to_char(ap.opendate,'MM-DD-YY') "Open Date"
from [databasname]
where ap.opendate between databasename-7 and databasename-1
I really don't want to change the date myself I want the system to know when the 7 days or month is.
View 4 Replies
View Related
Mar 13, 2009
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.
View 3 Replies
View Related
Sep 13, 2010
i create form on which 3 window, 3 Canvas, 3 blocks are there.
1)first block contain 3 items from_date and to_date(nondatabase)and find button
2)second block contains 3 items segment1, quantity and payment_terms
when i enter 2 dates and click on find button 2nd window open. segment1 and quantity between this 2 dates comes from base table like
select segment1,quantity
from po_lines_all pll, mtl_system_items mts
where pll.ITEM_ID=mts.INVENTORY_ITEM_ID
and creation_date between :FROM_DATE and :TO_DATE
and payment_terms should enter manually
3)2nd block contains summary button when i click on it, it save segment1, quantity, payment_terms and open new window which contain segment1 ,qty, payment_terms and when i click on summary button data comes from custom table which group clause means
suppose
item qty terms
a 2 2days
a 3 2days
b 3 3days
then on third form i shows like this
a 5 2days
b 3 3days
i done all this things but problem is that
suppose once data between 21-aug-2010 and 24-aug-2010 comes on second form , i enter payment_terms and save it. sumaary form also run properly but if another user enter date between 22-aug-2010 and 30-aug-2010 then it will shows data between 21-aug-2010 to 24-aug-2010 from custom table i.e with payment terms as earlier it save and from 25-aug-2010 to 30-aug-2010 it shows data from base table
like
item qty terms date
a 2 2days 21aug
a 3 2days 22aug
b 3 3days 24aug
b 4 NULL(ENTER MANUALLY) 26aug
c 5 NULL 27aug
View 1 Replies
View Related
Oct 5, 2013
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
View 8 Replies
View Related
Jun 28, 2011
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies
View Related
Jul 15, 2012
I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?
if not, that means I have to :
1- Add a text filed P1_PHONE
2- edit my report query to something similar to
> Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null
3- add button to refresh the report.
but the item P1_PHONE should be on the header of the Report region. is there a way to do this.
I am using theme 23
page template without sidebars
Report template : Reports Region.
how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.
View 7 Replies
View Related
Aug 9, 2013
How to force an index if the table not using the index?
View 10 Replies
View Related
Aug 2, 2012
Is the index suggested on the date column.
Here is the nature of the date column in my case:
1. The table populates with 1000's of records every day with date being always incremental (current date).
2. The search criteria from the weba application (ADF) is based on the date, user gives the range.
3. From ADF I am referring to it as sql.Timestamp when building the query.
Does Index suggested on the date column here and if so what type of index ?
View 4 Replies
View Related
Feb 14, 2013
Is there a way to find when was a database role created and who has created?
View 5 Replies
View Related
Aug 17, 2012
tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies
View Related
Jul 26, 2011
Is there a way I can find what the last date/time and index was used for a select...
I have a table with several indexes on them, which I beleive are not being accessed.
I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.
select index_name from dba_indexes where table_name='<table name>'
and index_name not in (select c1 from(
select p.object_name c1, p.operation c2, p.options c3, count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_name, p.operation, p.options
order by 1,2,3))
Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?
View 2 Replies
View Related
Jan 15, 2009
I have an application which deploys the data to Oracle database. It has more than 25 tables and many columns. It does not have any document explaining the deployment, so I am kind of doing reverse engineering here.
I need a script which will fetch the column name or at least table names which will match with either some string or number? I found few examples on net to find out number. But I am struggling to make it work for string.
I can not work on stored proc, as I do not have access to create that on server. So, any script will work.
View 3 Replies
View Related
Aug 9, 2013
I am trying to find a value in any column of any table ? How can i do that ? A value example would be 'FEDERAL' Using Oracle 11g and SQL Developer.
View 6 Replies
View Related
Aug 25, 2010
in one of the data base some of the column values are TE. i wanted to serch in what are the tables this TE values are present. so m running the below function
CREATE OR REPLACE FUNCTION find_in_schema(val VARCHAR2)
RETURN VARCHAR2 IS
v_old_table user_tab_columns.table_name%TYPE;
v_where VARCHAR2(4000);
v_first_col BOOLEAN := TRUE;
[code]....
but v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''' is giving me numaric or value error when i run as select find_in_schema('@TL') from dual; so how can i go ahed with the serch ?
View 2 Replies
View Related
Apr 28, 2013
I have a requirement that i should list out all the table names which are all using timestamp datatype in a specified schema. Is there any way to find those table names by using any system tables.
View 1 Replies
View Related
May 3, 2013
in a table how many foreign keys can we create.
View 10 Replies
View Related
May 18, 2012
I am working with following select clause:
select distinct S.ID ID
from
ods.hso_Scheduled H,
ods.SO_SCHEDULED S
where
S.insertion_date >= to_date('01-DEC-2011') and S.insertion_date < to_date('01-FEB-2012')
and H.ID=S.ID
Both the involved tables, HSO_SCHEDULED is having 15 million records and SO_SCHEDULED table is having 7 million records.
I have created following indexes on these tables:
Indexes on SO_SCHEDULED:
Index name Column name
SS_IDX1ID, SO_SUB_ITEM__ID
SS_IDX2INSERTION_DATE
SS_IDX3ID, INSERTION_DATE
SS_IDX4ID, SO_SUB_ITEM__ID, INSERTION_DATE
SO_SCHEDULED_ID_PKID
Indexes on HSO_SCHEDULED:
HSS_IDX1ID, SO_SUB_ITEM__ID, LAST_UPDATING_DATE
HSS_IDX2ID, LAST_UPDATING_DATE
HSS_IDX3ID
My problem is despite of having relevant indexes present, my query is not hitting them and hence the performance is very bad.
Explain Plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 574170360
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 814K| 38M| | 9574 (1)| 00:02:15 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 814K| 38M| | 9574 (1)| 00:02:15 | Q1,02 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 814K| 38M| 185M| 9574 (1)| 00:02:15 | Q1,02 | PCWP | |
|* 4 | HASH JOIN | | 2653K| 124M| | 9564 (1)| 00:02:14 | Q1,02 | PCWP | |
| 5 | PX JOIN FILTER CREATE| :BF0000 | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| SO_SCHEDULED | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | P->P | HASH |
| 12 | PX JOIN FILTER USE | :BF0000 | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| HSO_SCHEDULED | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("H"."ID"="S"."ID")
9 - filter("S"."INSERTION_DATE">=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"S"."INSERTION_DATE"<TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
View 6 Replies
View Related
Sep 4, 2012
I am trying to search a word which starts with 'FRA' in any columns and any tables.
I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.
I ahve looked into
Re: How to search in all rows and all columns?
Re: SQL Search Query?
but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view
select distinct substr (table_name, 1, 14) "Table",
substr (t.column_value.getstringval (), 1, 50) "Column/Value"
from all_cons_columns,
table
(xmlsequence
(dbms_xmlgen.getxmltype ('select ' || column_name
|| ' from ' || table_name
|| ' where upper('
|| column_name
|| ') like upper(''%' || 'fra'
|| '%'')'
).extract ('ROWSET/ROW/*')
)
) t
order by "Table";
running teh above query got me thsi error:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
View 4 Replies
View Related
Sep 21, 2011
I want to make partition of table which is already created and has huge amount of Data I have done indexing already
my structure are as follows
EMPLOYEE CHAR(10),
MONTH_YEAR NUMBER (6),
TYPE CHAR (1),
SALARY_CODE CHAR (5),
AMOUNT NUMBER (16,2)
Indexing on Employee,Month,Salary_Code
How can I do partitioning on this table.
View 1 Replies
View Related
Feb 8, 2011
somebody has created the table , who had created the table Note: not required the schema name,
View 4 Replies
View Related
May 1, 2013
I have data that the user is modifying on the form and a data block that is supposed to reflect those change (summaries) So I created
TYPE VehicleInfo IS RECORD (Vehicle_Desc ctl_fleet_vehicles.Vehicle_Desc%Type,
Miles timecapture.miles%Type,
Hours TimeCapture.Equipment_hours%Type);
TYPE VehicleTab IS TABLE OF VehicleInfo
INDEX BY BINARY_INTEGER;
VehicleObj VehicleTab;
I fill this up with the correct data, but I would like to be able to use this as a table in the data block (called Vehicle_Summary) In the past I have piped tables into data blocks from the server, but I'm unsure how to use a table I created in forms in the Query Data Source Name of the data block.
View 5 Replies
View Related
Aug 5, 2013
<ORACLE VERSION : 11.2.0.2.0> i have created a table with CLOB as datatype for one of the columns, I am trying to store a string ( I am not sure about the length of the string) , when i am querying on my table for the CLOB column,instead of the actual string "(HUGECLOB)" is coming. How to get the actual string in case the problem is with the SIZE.
View 1 Replies
View Related
