Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Select function-based index data as rows in a query?

J
jules last edited by
I'm trying to learn about indexes in Oracle 18c.

As a novice, it's occurred to me that it might be interesting to view the actual data that's in a function-based index:
```
create index sidewalk_endpoint_list_idx on my_owner.sidewalks(my_owner.endpoint_list(shape),objectid); 
```
Is there a way to SELECT an index's data in a query?

Fake example:
```
select
    *
from
    sidewalk_endpoint_list_idx
```
That might be a silly question. Maybe indexes aren't stored in tables. But I thought it was worth asking, just so I can learn.
Reply Quote 0
1 Reply Last reply
K
Kadyn last edited by
You can't, but you can select it from the table:
```
select my_owner.endpoint_list(shape),objectid,rowid
  from my_owner.sidewalks;
```
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

E

How far should bind variables go in Oracle?
SQL, Database Testing • oracle plsql oracle 11g r2 • • emerson

2

0
Votes

2
Posts

0
Views

Use a bind variable anywhere that you might actually change the value that is passed in. It probably doesn't make sense, for example, to use a bind variable in the EXISTS clause in your third example because it seems extremely unlikely that anyone would ever want to pass in a different constant for the EXISTS clause. In your second example, it probably does make sense to use a bind variable rather than a hard-coded status assuming that the application(s) are likely to want to run this query for different id and status values. On the other hand, if the code would only ever pass in a status of 2 for this query, it probably doesn't make sense to use a bind variable. Assuming that status is not evenly distributed, you'd be better off giving the optimizer the information that you're always going to use a status of 2 rather than trying to rely on bind variable peeking to give the optimizer that information. If the value is never going to change-- i.e. if the status in your second query will always be 2-- you can execute the query as many times as you'd like and it will only appear once in v$sql or v$sqlarea. In my case, I'll execute the query 10 times SQL> ed Wrote file afiedt.buf 1* select * from v$sql where sql_text like '%jc_bind_test%' SQL> select /* jc_literal_test */ count(*) 2 from emp 3 where ename = 'KING'; COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 SQL> / COUNT(*) ---------- 1 And from v$sql or v$sqlarea, you'll see only one row for the query that has the hard-coded literal. That row will show that the query was executed 10 times SQL> ed Wrote file afiedt.buf 1* select sql_text, executions, loaded_versions, parse_calls from v$sqlarea where sql_text like '%jc_literal_test%' SQL> / SQL_TEXT EXECUTIONS LOADED_VERSIONS PARSE_CALLS ---------------------------------------- ---------- --------------- ----------- select * from v$sql where sql_text like 1 1 1 '%jc_literal_test%' select sql_text, executions, loaded_vers 5 1 5 ions, parse_calls from v$sql where sql_t ext like '%jc_literal_test%' select sql_text, executions, loaded_vers 1 1 1 ions, parse_calls from v$sqlarea where s ql_text like '%jc_literal_test%' select /* jc_literal_test */ count(*) 10 1 10 from emp where ename = 'KING'
G

Oracle SQL Creating Copy of Table Using DBMS_metadata
SQL, Database Testing • oracle table ddl dbms • • gionna

2

0
Votes

2
Posts

0
Views

C

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1: create table x.table1 (id number, name varchar2(100), primary key(id)); You want to recreate it as Y.TABLE2, you could do the following: set serveroutput on declare metadata_handle number; transform_handle number; ddl_handle number; result_array sys.ku$_ddls; begin metadata_handle := dbms_metadata.open('TABLE'); transform_handle := dbms_metadata.add_transform(metadata_handle, 'MODIFY'); dbms_metadata.set_remap_param(transform_handle, 'REMAP_NAME', 'TABLE1', 'TABLE2'); dbms_metadata.set_remap_param(transform_handle, 'REMAP_SCHEMA', 'X', 'Y'); ddl_handle := dbms_metadata.add_transform(metadata_handle, 'DDL'); dbms_metadata.set_filter(metadata_handle, 'SCHEMA', 'X'); dbms_metadata.set_filter(metadata_handle, 'NAME', 'TABLE1'); loop result_array := dbms_metadata.fetch_ddl(metadata_handle); exit when result_array is null; for i in result_array.first..result_array.last loop dbms_output.put_line(result_array(i).ddltext); end loop; end loop; dbms_metadata.close(metadata_handle); end; / Which results in: CREATE TABLE "Y"."TABLE2" ( "ID" NUMBER, "NAME" VARCHAR2(100), PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USERS" ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" Obviously you can pass it to execute immediate, or just spool it.
C

Select row if column data was not selected
SQL, Database Testing • postgresql postgresql 9.3 • • charlenef

2

0
Votes

2
Posts

0
Views

This can easily be done using Postgres' distinct on operator: select distinct on (book_id) name, title, bookd_id, date from the_table order by book_id, date; This will pick the first row for each book_id based on the date column.
How can I change three hundred procedures at once?
SQL, Database Testing • oracle stored procedure orac languagele 10g • • Demir

2

0
Votes

2
Posts

0
Views

C

If you script the procedures out to a file, the search/replace can be trivially dome with a sed script along the lines of s/\/file\/folder\/documents/directory_name/g (note not tested, just off the top of my head, but you can fiddle with it). Then you can re-load the stored procedures. Note that if you're frigging with the code base you should really test what you're doing rather than doing a blind search/replace on production code. What could possibly go wrong?
A

SQLITE: Select row ID based on only when the column all the values are same
SQL, Database Testing • sqlite select • • Anderson

2

0
Votes

2
Posts

0
Views

M

Edit I should not get the result of ttid 5 because it has status as 'preparing'. I should get ttids results only when all the statuses are 'done'. To get only the ones where done is found twice and no other statuses are found: SELECT tb, tbid, ttid,status FROM test t1 WHERE status = 'done' AND NOT EXISTS(SELECT * FROM test t2 where t2.status != 'done' and t1.ttid = t2.ttid) GROUP BY tb,tbid,ttid,status HAVING COUNT(*) > 1; DB-Fiddle Or SELECT ttid,status FROM test t1 WHERE status = 'done' AND NOT EXISTS(SELECT * FROM test t2 where t2.status != 'done' and t1.ttid = t2.ttid) GROUP BY ttid,status HAVING COUNT(*) > 1; DB-Fiddle The reasoning for adding it to the group by or applying a function: if tb or tbid can be different for the same 'done' status and the same ttid, which one do you choose? Do you choose the smallest, the biggest, ...? An example of what happens if this is true and it is not added to a group by or function: DB-Fiddle that fails
E

Site Wardpress in the cap/rus/
Software Programming • php wordpress index • • elysha

2

0
Votes

2
Posts

0
Views

Use or multiply website https://wp-kama.ru/handbook/wordpress/ustanovka-multisite-mu or any of the plastics for multilingualism. For example: WPML https://wpml.org/ru/ ♪
J

How to calculate two subquery value in Oracle SQL
Software Programming • sql oracle orac languagele11g orac languagele12c langua orac languagele18c langua • • jules

2

0
Votes

2
Posts

0
Views

Z

Try to perform a subselect of this information:select dados1+dados2 from (Select (select seuselectdados1) as dados1 , (select seuselectdados2) as dados2 from table ) as tab_temp
Removing repeated oracle data
Software Programming • oracle • • Analeea

2

0
Votes

2
Posts

0
Views

You could do the following:select min(id) id, nombre, precio from productos p left join (select nombre, precio, count(1) from productos group by nombre, precio having count(1) > 1 ) dup on dup.nombre = p.nombre and dup.precio = p.precio group by nombre, precio The select more internal would bring precios and nombres that were duplicates, then we do join with products to have id of duplicates, and in the last select, we group and get the minor id for each nombre and precio. That I understand should be id to eliminate (delete productos where id in (...)). Logically in doing so we wouldn't be contemplating if for example there are more than two duplicates, in that case we could stay with the max(id) that would be the one who should "survive" and eliminate the others who agree on nombre and precio And don't say ìd
M

Single MySQL query with row averages based on conditions
SQL, Database Testing • mysql subquery • • meriah

2

0
Votes

2
Posts

0
Views

A

Keep the raw data in MySQL. But plan on coding the logic in PHP (or whatever). You have only begun to touch on how complex the math can get. What about the blood pressure readings, plus whether the patient is/was taking a BP lowering drug?
B

How do I determine the correct index on MS-SQL-Server based on a select?
SQL, Database Testing • sql server sql server 2019 • • Burma

2

0
Votes

2
Posts

0
Views

J

You are unlikely to ever see index use because of this in the query: CONVERT(VARCHAR(6), v1.Tag, 112) Because it has to calculate the values from v1.Tag, it can't simply use the histogram in the statistics to determine row counts. It must scan the entire table to find all the values that match, and it has to do the conversion each time. If u1.Monat and v1.Tag are not the same data type, adding a column that is the same data type might be necessary (on either u1 or v1, whichever). However, the bigger issue is, you're moving all the data. The query has no WHERE clause to filter anything. Without a filter, the most efficient way to move everything is through scans. Index seeks wouldn't make sense. You might see some index usage on the JOINs between tables (once you fix the CONVERT problem), but possibly not since all the rows are being moved.
R

Oracle, Is there a way to request a lock with alter table drop partition?
SQL, Database Testing • oracle ddl • • Raziyah00

2

0
Votes

2
Posts

0
Views

R

Just user the ddl_lock_timout feature/parameter (11g+): alter session set ddl_lock_timeout=1000000; -- long long time alter table SCH.DATA_TABLE drop partition SYS_P1026632 ; The value for ddl_lock_timeout means seconds to wait before the ORA-00054 is raised or the command completes (which is desirable...). Te default is 0 (which means no wait). DDL With the WAIT Option (DDL_LOCK_TIMEOUT)
C

PostgreSQL Select value row depending on another value row
SQL, Database Testing • postgresql • • courtlanda

2

0
Votes

2
Posts

1
Views

J

You can use NOT LIKE to include all the non taxi related data -- check that the phase doesn't begin with TAXI_. Then add for each size a comparison that couples the airport size to the taxi route with OR. SELECT * FROM aviation.emissionsco2 WHERE phase NOT LIKE 'TAXI$_%' ESCAPE '$' OR (phase, type) IN (('TAXI_LARGE', 'large_airport'), ('TAXI_MEDIUM', 'medium_airport'), ('TAXI_SMALL', 'small_airport'));
A

SELECT COUNT in same table based on current row value within a single query
SQL, Database Testing • mysql count • • Avante

2

0
Votes

2
Posts

0
Views

K

Your example data doesn't correspond with your reszkt. But you are looking for a INNER JOIN with a subquery CREATE TABLE table1 ( `uid` INTEGER, `value` VARCHAR(6), `date_added` VARCHAR(10) ); INSERT INTO table1 (uid, value, date_added) VALUES ('14152', 'NA4156', '2019-03-01'), ('14153', 'EA4656', '2019-03-02'), ('14152', 'EA4656', '2019-03-02'), ('14155', 'CA4456', '2019-03-03'), ('14153', 'DA4556', '2019-03-03'), ('14157', 'EA4656', '2019-03-03'), ('14155', 'FA4756', '2019-03-06'), ('14159', 'GA4856', '2019-03-06'); ✓ ✓ SELECT t1.`uid`, t1.`value`, total_count FROM table1 t1 InnER JOIN (SELECT Count(*) total_count, `uid` FROM table1 GROUP BY `uid`) t2 ON t1.`uid` = t2.`uid` uid | value | total_count ----: | :----- | ----------: 14152 | NA4156 | 2 14153 | EA4656 | 2 14152 | EA4656 | 2 14155 | CA4456 | 2 14153 | DA4556 | 2 14157 | EA4656 | 1 14155 | FA4756 | 2 14159 | GA4856 | 1 db<>fiddle here
B

Select row from one query where opposites are not present in second query
SQL, Database Testing • mysql • • Burma

2

0
Votes

2
Posts

0
Views

R

SELECT m.id FROM message AS m WHERE NOT EXISTS ( SELECT * FROM inbox AS i WHERE i.message_id = m.id AND i.to_user_id = m.from_user_id ) I think it could also be cone with a LEFT JOIN (with similar efficiency), but NOT EXISTS more closely matches your description of the task.
Select only those rows based on specific condition of user id
SQL, Database Testing • mysql greatest n per group google bigquery • • Bogopo

2

0
Votes

2
Posts

0
Views

K

SELECT DISTINCT t1.* FROM `table` t1, `table` t2 WHERE t1.id = t2.id AND t1.timestamp > t2.timestamp AND t2.activity = 'Tv';

Select function-based index data as rows in a query?

Suggested Topics

How far should bind variables go in Oracle? SQL, Database Testing • oracle plsql oracle 11g r2 • • emerson

Oracle SQL Creating Copy of Table Using DBMS_metadata SQL, Database Testing • oracle table ddl dbms • • gionna

Select row if column data was not selected SQL, Database Testing • postgresql postgresql 9.3 • • charlenef

How can I change three hundred procedures at once? SQL, Database Testing • oracle stored procedure orac languagele 10g • • Demir

SQLITE: Select row ID based on only when the column all the values are same SQL, Database Testing • sqlite select • • Anderson

Site Wardpress in the cap/rus/ Software Programming • php wordpress index • • elysha

How to calculate two subquery value in Oracle SQL Software Programming • sql oracle orac languagele11g orac languagele12c langua orac languagele18c langua • • jules

Removing repeated oracle data Software Programming • oracle • • Analeea

Single MySQL query with row averages based on conditions SQL, Database Testing • mysql subquery • • meriah

How do I determine the correct index on MS-SQL-Server based on a select? SQL, Database Testing • sql server sql server 2019 • • Burma

Oracle, Is there a way to request a lock with alter table drop partition? SQL, Database Testing • oracle ddl • • Raziyah00

PostgreSQL Select value row depending on another value row SQL, Database Testing • postgresql • • courtlanda

SELECT COUNT in same table based on current row value within a single query SQL, Database Testing • mysql count • • Avante

Select row from one query where opposites are not present in second query SQL, Database Testing • mysql • • Burma

Select only those rows based on specific condition of user id SQL, Database Testing • mysql greatest n per group google bigquery • • Bogopo

How far should bind variables go in Oracle?
SQL, Database Testing • oracle plsql oracle 11g r2 • • emerson

Oracle SQL Creating Copy of Table Using DBMS_metadata
SQL, Database Testing • oracle table ddl dbms • • gionna

Select row if column data was not selected
SQL, Database Testing • postgresql postgresql 9.3 • • charlenef

How can I change three hundred procedures at once?
SQL, Database Testing • oracle stored procedure orac languagele 10g • • Demir

SQLITE: Select row ID based on only when the column all the values are same
SQL, Database Testing • sqlite select • • Anderson

Site Wardpress in the cap/rus/
Software Programming • php wordpress index • • elysha

How to calculate two subquery value in Oracle SQL
Software Programming • sql oracle orac languagele11g orac languagele12c langua orac languagele18c langua • • jules

Removing repeated oracle data
Software Programming • oracle • • Analeea

Single MySQL query with row averages based on conditions
SQL, Database Testing • mysql subquery • • meriah

How do I determine the correct index on MS-SQL-Server based on a select?
SQL, Database Testing • sql server sql server 2019 • • Burma

Oracle, Is there a way to request a lock with alter table drop partition?
SQL, Database Testing • oracle ddl • • Raziyah00

PostgreSQL Select value row depending on another value row
SQL, Database Testing • postgresql • • courtlanda

SELECT COUNT in same table based on current row value within a single query
SQL, Database Testing • mysql count • • Avante

Select row from one query where opposites are not present in second query
SQL, Database Testing • mysql • • Burma

Select only those rows based on specific condition of user id
SQL, Database Testing • mysql greatest n per group google bigquery • • Bogopo