DBA Blogs

Domain Indexes -- 3 : CTXCAT Index

Hemant K Chitale - Sat, 2018-04-21 11:14
In previous posts in December 2017, I had demonstrated a CONTEXT Index.

A CONTEXT Index is used for full-text retrieval from large pieces of text (or document formats stored in LOBs)

A CTXCAT Index is best suited for small fragments of text that are to be indexed with other relational data.

Before I begin with the CTXCAT index, in addition to the CTXAPP role (that I had granted during the earlier demonstration), the account also needs the CREATE TRIGGER privilege.

SQL> grant ctxapp to ctxuser;

Grant succeeded.

SQL> grant create trigger to ctxuser;

Grant succeeded.

SQL>


I can now proceed with the CTXUSER demonstration.

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table books
2 (book_id integer primary key,
3 book_title varchar2(250) not null,
4 book_author varchar2(80),
5 book_subject varchar2(25),
6 shelf_id integer)
7 /

Table created.

SQL>
SQL> insert into books values
2 (1,'A Study In Scarlet','Arthur Conan Doyle','Mystery',1);

1 row created.

SQL> insert into books values
2 (2,'The Sign Of Four','Arthur Conan Doyle','Mystery',1);

1 row created.

SQL> insert into books values
2 (3,'Murder On The Orient Express','Agatha Christie','Mystery',1);

1 row created.

SQL> insert into books values
2 (4,'A Brief History of Time','Stephen Hawking','Science - Physics',2);

1 row created.

SQL>
SQL> insert into books values
2 (5,'2001: A Space Odyssey','Arthur C Clarke','Science Fiction',3);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>


Next, I specify what is called an Index Set -- which specifies the structured columns that are to be included in the CTXCAT Index.  I then define the CTXCAT Index on the BOOK_TITLE column.

SQL> begin
2 ctx_ddl.create_index_set('books_set');
3 ctx_ddl.add_index('books_set','book_subject');
4 ctx_ddl.add_index('books_set','shelf_id');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> create index books_title_index
2 on books (book_title)
3 indextype is ctxsys.ctxcat
4 parameters ('index set books_set')
5 /

Index created.

SQL>


Now, I can use the Index to query the table, using the CATSEARCH clause instead of the CONTAINS clause. My query includes both BOOK_TITLE and SHELF_ID

SQL> select book_title,book_author,book_subject,shelf_id
2 from books
3 where catsearch (book_title,'History','shelf_id=1') > 0
4 /

no rows selected

SQL> select book_title,book_author,book_subject,shelf_id
2 from books
3 where catsearch (book_title,'History','shelf_id>1') > 0
4 /

BOOK_TITLE
--------------------------------------------------------------------------------
BOOK_AUTHOR
--------------------------------------------------------------------------------
BOOK_SUBJECT SHELF_ID
------------------------- ----------
A Brief History of Time
Stephen Hawking
Science - Physics 2


SQL>


The CTXCAT Index that I built on BOOK_TITLE also includes BOOK_SUBJECT and SHELF_ID as indexed columns by virtue of the INDEX_SET called "BOOKS_SET".

Now, I add another row and verify if I need to Sync the index (as I had to do with the CONTEXT Index earlier).

SQL> insert into books
2 values
3 (6,'The Selfish Gene','Richard Dawkins','Evolution',2);

1 row created.

SQL> commit;
SQL> select book_title,book_author,book_subject,shelf_id
2 from books
3 where catsearch (book_title,'Gene','book_subject > ''S'' ') > 0
4 /

no rows selected

SQL> select book_title,book_author,book_subject,shelf_id
2 from books
3 where catsearch (book_title,'Gene','book_subject > ''E'' ') > 0
4 /

BOOK_TITLE
--------------------------------------------------------------------------------
BOOK_AUTHOR
--------------------------------------------------------------------------------
BOOK_SUBJECT SHELF_ID
------------------------- ----------
The Selfish Gene
Richard Dawkins
Evolution 2


SQL>


Note, specifically, how I could use the BOOK_SUBJECT in the query as if looking up a separate index on BOOK_SUBJECT.
The new book was included in the index without a call to CTX_DDL.SYNC_INDEX as would be required for the CONTEXT IndexType.

The portion of the query that is on the BOOK_TITLE column does a Text search on this column but the portions on BOOK_SUBJECT an SHELF_ID behave as with regular indexes.


(I know  that some readers will dispute the subject categorization "Evolution"  but I deliberately threw that in so that I  could show a query that uses a predicate filter not on "Science").

.
.
.




Categories: DBA Blogs

GCP - How to manage SSH keys on VM Instance?

Surachart Opun - Fri, 2018-04-20 23:13
On Google Cloud Platform, adding SSH keys in Metadata (project-wide public SSH keys). It can help to ssh to every VM instances on Compute Engine easily but it's not a good idea. We are able to do for test, but should not use on Production. We should add SSH Key in OS login. 
https://cloud.google.com/compute/docs/instances/adding-removing-ssh-keys#risks



Question:
How can we block SSH Keys from Metadata(project-wide public SSH keys) on VM instance?
Answer: We can block by checking "Block project-wide SSH keys" on each instance.

In case we have SSH Key on metadata. We are able to ssh by using private key and login like.

So, we block it... On "Compute Engine" - "VM Instances", click [instance name] and "Edit". To check "Block project-wide SSH keys" and "Save".


VM instance should refuse Key what 's not in SSH  Keys of VM instance. (You can remove SSH Keys of project owner on instance, but it will be automatic added when you click "SSH" on GUI).

Additional, we should review and remove SSH Keys in metadata(project-wide public SSH keys), if we ensure we have not used. (Don't remove ssh key of project owner).

After removing, We would like to add SSH Key and don't want to add it in OS login. We are able to add it in SSH Keys on Instance like.


Assume: username is "myuser".

First of all, we have to generate Private and Public Keys. Example uses "PuTTY Key Generator". Because I use "Putty.


Then "Save private key" (We have to use when putty to server) and "Save public key".

To use public key on VM instance, click "Add item".



Example: It's [public key] [username]  

 then "Save".
Note: In picture, it's highlight about [username]

Open "putty", select "Private key file for authentication", fill in ip address and connect.

it's easy, right?
myuser@centos7:~$ id
uid=1003(myuser) gid=1004(myuser) groups=1004(myuser),4(adm),30(dip),44(video),46(plugdev),1000(google-sudoers)If we use command "id [user in project-wide SSH keys], we still see it, but it's unable to ssh on this VM instance.
myuser@centos7:~$ id opun
uid=1001(opun) gid=1002(opun) groups=1002(opun),4(adm),30(dip),44(video),46(plugdev),
Reference:
Categories: DBA Blogs

Relocate Goldengate Processes to Other Node with agctl

Pakistan's First Oracle Blog - Fri, 2018-04-20 22:00
Oracle Grid Infrastructure Agents can be used to manage Oracle Goldengate through Oracle GI. agctl is the utility to add, modify and relocate the goldengate. These Oracle GI agents can also be used with other products like weblogic, mysql etc. 


Frits has a good article about installation and general commands regarding GI agents for a non-clustered environment.

Following is the command to relocate Goldengate processes to other node with agctl. 


[gi@hostname ~]$ agctl status goldengate [service_name]
[gi@hostname ~]$ agctl config goldengate [service_name] [gi@hostname ~]$ agctl relocate goldengate [service_name] --node [node_name] [gi@hostname ~]$ agctl config goldengate [service_name] [gi@hostname ~]$ agctl status goldengate [service_name]

Hope that helps.
Categories: DBA Blogs

utl_http.begin_request results in protocol error when url size is big

Tom Kyte - Fri, 2018-04-20 17:26
Hi, while using utl_http package, we are able to make calls to a 3rd party webservice and all was going good till we hit transaction which resulted in big URL size - for ex one transaction had multiple rejections and url size is bigger than normal ...
Categories: DBA Blogs

Upgrade to 12c - High Fetch time vs. Low execution time

Tom Kyte - Fri, 2018-04-20 17:26
Hi Tom, We are migrating our databases from Oracle 11.2.0.3 to Oracle 12.1.0.2.0R1 on Exadata and after we did this, we are seeing extreme slowness in loading 3 of our application screens, even though the queries are running as or more efficiently...
Categories: DBA Blogs

Partner Webcast – Oracle Container Native Application Development Platform – Use with Kubernetes

Containerization of cloud applications rapidly becomes the right (only) way to deploy complex systems architected with microservice approach in mind. Containers solve one of the fundamental issues of...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partitioning vs Indexing

Tom Kyte - Thu, 2018-04-19 23:06
Hi Tom, i have a question in partitioning by list of a table. I have a set of tables which need to be hystoricized once a new record is inserted: then i have a STATUS column which flag an active status (AT) and a historic one (ST). To match this re...
Categories: DBA Blogs

Oralce Open v$open_cursor counts simple "updates" as open with the use of a cursor (open, execute, fetch, close, commit)

Tom Kyte - Thu, 2018-04-19 23:06
I am checking for open cursors while running our client server application (application info below) with the query below and noticed that a simple ?update? without the use of any cursors shows as open cursor. When another ?update? is issued its repla...
Categories: DBA Blogs

Performance issue/session getting hang

Tom Kyte - Thu, 2018-04-19 23:06
Hi Tom, I have a table having around 5 million records. Table Structure : DESC RPT_MSG_CHANGE <code>Name Null Type ---------- -------- -------------- OID NOT NULL NUMBER PRODUCT NOT NULL VARCHAR2(20)...
Categories: DBA Blogs

Long Raw to BLOB

Tom Kyte - Thu, 2018-04-19 04:46
Hi Tom, We are using an Oracle 8.1.7 database. Is there a way in PL/SQL or Java Stored Procedure to convert a Long Raw into a BLOB? Thanks, Firas Khasawneh
Categories: DBA Blogs

Bug in Export Wizard?

Tom Kyte - Wed, 2018-04-18 10:26
When using the export wizard and browsing to an existing file that I want to overwrite with my new data results, I find when I select that specific file from the Export File Chooser window and click SAVE, the file that is actually selected is some ot...
Categories: DBA Blogs

dbms_stats and optimize techniques

Tom Kyte - Wed, 2018-04-18 10:26
I am setting the degree parameter of dbms_stats.gather_table_stat. could any one tell me that how we can calculate the value of this parameter and how this is link with the hint optimize techniques.
Categories: DBA Blogs

Oracle DBAs and GDPR

Pakistan's First Oracle Blog - Wed, 2018-04-18 01:32
The General Data Protection Regulation (GDPR) (Regulation (EU) 2016/679) is a regulation by which the European Parliament, the Council of the European Union and the European Commission intend to strengthen and unify data protection for all individuals within the European Union (EU).


To bring Oracle database to align with GDPR directive, we have to encrypt all the databases and files on disk, aka encryption at rest (when data is stored). We also have to encrypt the database network traffic. 

The Transparent Data Encryption (TDE) feature allows sensitive data to be encrypted within the datafiles to prevent access to it from the operating system. 

You cannot encrypt an existing tablespace. So if you wish to encrypt existing data, you need to move them from unencrypted tablespaces to encrypted tablespaces. For doing this you can use any of following methods:

i) Oracle Data Pump utility.
ii) Commands like CREATE TABLE...AS SELECT...
iii) Move tables like ALTER TABLE...MOVE..  or rebuild indexes.
iv) Oracle Table Redefinition.

In order encrypt network traffic between client and server we have 2 options from Oracle:

i) Native Network Encryption for Database Connections
ii) Configuration of TCP/IP with SSL and TLS for Database Connections

Native Network Encryption is all about setting sqlnet.ora file and doesn't have the overhead of second option whereyou have to configure various network files at server and client and also have to obtain certificates and create wallet. In first option, there is possibility of not gurantee of encryption, whereas in second there is gurantee of encryption. 
Categories: DBA Blogs

AWS Pricing Made Easy By Simple Monthly Calculator

Pakistan's First Oracle Blog - Wed, 2018-04-18 01:26
With ever changing pricing model and services, its hard to keep track of AWS costing.





If you want to check how much would it cost to have a certain AWS service, tailored to your requirement then use the following Simply Monthly Calculator from AWS.

AWS Price Calculator.
Categories: DBA Blogs

Best way to index uuid

Tom Kyte - Tue, 2018-04-17 16:06
Hello, What is the best way to index uud if I only do equal comparaison on it ? I gess that Hash index is better but I'm not sure. Regards Stephane GINER
Categories: DBA Blogs

Order by at runtime

Tom Kyte - Tue, 2018-04-17 16:06
Hello, we have some huge tables to query, and with order by clause (must be used) it takes a very long time for a query to be done. as I know that we can do the order by at run time using dynamic SQL, but my questions are: 1. do we have any o...
Categories: DBA Blogs

Automatic list partitioning

Tom Kyte - Tue, 2018-04-17 16:06
Hi Tom! I use Oracle 12c version. I have partitioned by list table. How can I change non automatic partitioning to automatic? Thank you!
Categories: DBA Blogs

how to generate .dsv files using SQL script?

Tom Kyte - Tue, 2018-04-17 16:06
we have around 100 table out of 200, in which there is a column date. what we want is, first we want to chagen the <b>NLS_date_format to DD-MON-YYYY HH12:MI:SS AM</b>(using script) then save the tables with date in a .DSV files. also n...
Categories: DBA Blogs

Julian Date Full Explanation

Tom Kyte - Mon, 2018-04-16 21:46
Hello, I'm fairly new, but I have been finding bits and pieces on Julian date conversion, but not a full explanation of the Julian date conversion? <b>I.E TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYDDD'))-1900000</b> Firstly, the SYSDATE is using the T...
Categories: DBA Blogs

Help needed with match_recognize

Tom Kyte - Mon, 2018-04-16 21:46
Dear Mr. Tom, Thank you for all your help and time in supporting our requests. I have some issues with MATCH_RECOGNIZE Oracle Version - 12.1.0.2.0 OS - REDHAT Linux <code>CREATE TABLE test_match_recognize(employment_id NUMBER (10, 0) NOT N...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs